How to calculate the total number of descendants of an item?

I think there may be a simpler way here.

If you create a column called “# of Descendants”, set it to select rows with Parent.contains(thisRow) and then sum their # of descendants +1 each, you’ll get an automatic recursion down to the bottom.

Num Of Descendants Column Formula:
ThisTable.Filter([Parent Item].Contains(thisRow)).[Num of Descendants].Sum() + ThisTable.Filter([Parent Item].Contains(thisRow)).[Num of Descendants].Count()

1 Like

I have to bow, congrats for this efficiency @Billy_Jackson !
I got stuck in my reasoning when I said myself “I have to count until there is no child any more”, and that led me to the while loop. Sometimes brain not enough open-minded !

@Fran_Vidicek, I set Billy’s MUCH BETTER solution in the embed doc also :wink:

Of course, given the simplicty of it, do use it !!

Cheers

2 Likes

I already tried something like this, had the same idea but it only went 2 levels deep and now I got the same result, but I might have done something wrong.

Give me a minute, I will try again.


Oh, ok I see now, thanks so much @Billy_Jackson !!! That’s perfect!

image

1 Like

I don’t think the formula works, I believe you accidentally referenced to a wrong column.
Because if I delete the Number of Descendants (Desired Result) column, Billy’s Solution is an error.

And if I reference it to the right Number of Descendants column, it gives an error as well. Sum() function needs to be a number or something.

Or am I missing something?

I think there is an extra column at play here. The key to this working is that the formula is summing ITS OWN COLUMN. So in this case, it should be summing “Billy’s Solution” rather than a separate filtered column

1 Like

Yes that’s it! :hugs:
Thanks once again.

Hey — just seeing this. The screenshot in your OP is coming from my “tree chart”, right?

There’s a hidden column to look up children; it’s rather straightforward:
image

To calculate the number of children recursively, all you need to do is make another column, Cumulative to calculate cumulative children:

ListCombine(
  thisRow.[_Children (visible)],
  thisRow.[_Children (visible)].Cumulative
)

and then .Count() them:

and that’s it :slight_smile:

3 Likes

Yes it’s from the “three chart” doc. I went there for solution or ideas at first because it deals with similar structure :slightly_smiling_face:. I didn’t find the solution but it proved itself useful for visualizing the issue :+1:.


However now I have another problem/question if anyone is interested in helping solve. It's similar to precious problem but goes a level further:

Imagine we have boxes. Each box can have boxes and toys inside of it. The toys can be inside other boxes or in the box itself.
How would we count total number of toys inside a box?

I have explained the issue in detail in the same doc I shared in OP on a different page called “Problem 2”.

I’m assuming this time It won’t be possible to create a “non-dynamic” solution and we will have to approach it in a way that is more similar to @Quentin_Morel 's solution here. :thinking:

I think you can actually still take a similar approach.

We’re looking for Total Number of Toys inside box = Toys Directly in Box + Sum of toys directly in boxes of all descendants.

As we’ve seen, it’s pretty straightforward to calculate either the list of descendants (as per Paul’s solution) or the sum of items within them.

Here, you’re looking to create a column Total Toys Within =
Toys.filter([Parent Box].contains(thisRow)).Count() // Total Toys directly in box
+ Boxes.filter([Parent Box].contains(thisRow)).[Total Toys Within].Sum() // Sum of all toys in descendant boxes

1 Like

That only seems to go 2 levels deep.

If I didn’t do something wrong It only counts :
Total toys directly in box + Number of toys in immediate descendant boxes

The formula doesn’t have that “cascading” :ocean: effect.

I have put the attempt in the doc if you don’t mind checking If I missed something :pray:

Yes, another column has snuck in there again and spoiled our recursivity.

Try this here:

1 Like

The key principle here for both of these examples has been to create recursion without writing for/while loops by writing a formula that references other iterations of itself.

So RowA.RecursionFormulaColumn = functionOf(rowN.RecursionFormulaColumn)
This rowN is - itself - a function of other iterations of that column, so on and so on, as determined by the parent/child structure.

I don’t know how Coda’s formulas are implemented, but my best guess is that they are almost certainly performing a version of Quentin’s solution, but on the back end of things.

1 Like

I don’t think I understand.

Even if I put this formula in only one column it doesn’t give desired result. I don’t know if that’s what you thought when you said:

Have you tried putting that formula within the column named [Total Toys Within]?
I’m seeing it working on my end if I do that.

1 Like

Yep that works :confounded::confused:
Thank you soo much :smile:!

1 Like

My pleasure and happy Coda-ing!

1 Like

In case you’re still confused —

Coda allows for recursive behavior. You can reference the same column within that column’s formula but only as long as you’re reading the values through a lookup somewhere.

See in my previous message how “Cumulative” is calculated with the use of “Cumulative” of the child nodes.

Here’s a clip from one of my lectures where I’m demonstrating the recursive behavior with digest cycles (aka recalculation passes). I was solving this problem btw. Watching at 1.5x advised.

P.S. I think you can totally apply the same thinking to solve it even if you have multiple tables there at play. This demo in the video actually had two tables interplaying so that @Connor_McCormick1 would accurately describe the composition. I always say it’s primary that you understand the principle, not just the formula someone gave you in the community :slight_smile:

3 Likes

Thanks for that, that was actually very useful.
I didn’t know about _Delay() function as well, that’s nice to know.

P.S. щасливого Різдва! :christmas_tree:

1 Like

Ok that is so cool.
image

1 Like