Roll Up summation of all childs, grandchildren, grand grand children and so on

AppSheet AppSheet Q&A
1

Hi Everyone,

I have a table with these headers:

obj as key

f_ra as decimal

up as the obj value of a row that that is the parent of [-thisrow]

We get in this way a hierarchy that can go down forever as an example

I cannot get my head around on how to create a recurring formula of a virtual column that gets a rollup summation of F_RA from the lowest child to the oldest parent including the parents on the way

So for example, If obj A has F_RA 1 and has B and C as children with F_RA as 1 then B and C will have up A The summation I will ge then is 1 from A plus the 1+1 from children B and C. In my case the hierarchies can be 10 levels deep but this is not consistent one object might have 2 children while others can have 5 for example.

Any ideas?

All the best,

Andrei

2

hierarchy.png
hierarchy.png880Ă—1525 36.4 KB

Hierarchy example

3

I don’t believe this is possible from within AppSheet itself.

4

Hi Steve,

Thanx for the reply.

I’ll be testing some more and if i find it I’ll post it ihere. I am thinking to create a list of all descendants of the top of the hierarchy and build the sum from there. I have a way to define the children with no other children so i have the key values for the bottom of the hierarchy, but the I get stuck when i need to have recurring function upwards.

1 Like
5

Hi again,

I solved in this way that maybe is not the most elegant:

  • create virtual column with direct children DC=REF_ROWS(“Table”,“up”)
  • create a virtual column for direct descendants Descendants=LIST(DC)
  • create virtual columns with children of children Select(table[DC], AND(ISNOTBLANK([Descendants]),IN([obj],[_THISROW].[Descendants]))) and so on to the the last level
  • made a list of all descendants [Descendants]+[DEscendant2]+[Descendants3]+…
  • sum all the values of object within all descendants

I have 7000 rows so it takes a while to get all the virtual columns working but then it working perfectly with the summation.

2 Likes
6

You may use a physical column. But you will have to manually setup crud updates reference back to parent row. Also use a row update on click so it gets updated each time you open the record showing real time value reducing sync time.

7

Thanx Rifad,

The table stems from a BigQuery that updates everyday so I am working only with virtual columns. I add ref records to each row that needs to be processed. But I will check the sync time tip.