Question

Calculation Help - Is there a function or a way to count number of child of a list member

For eg, if I have State - City Hiearchy

How can I count how many city does a state have using a formula?

rob_marshall · Accepted Answer

@sagarkpr

Yes, there are a couple of ways to do it…If you already have the hierarchy created (Cities rolling up to State), create a line item in a module named Count (formatted as number) and hardcode the formula to 1 with the summary defined as SUM.  At the State level, you will have a total.

On the other hand, if you have all Cities and States in the same "flat" list, then in a SYS Module dimensionalized by that flat list, make sure you have a line item named Parent (which is loaded in from an action) and is a list formatted to the flat list,  and the line item Count hardcoded to 1.  In another module which is dimensionalized by the same Flat list, create a line item name Nbr of Children with the following formula:  SYS Flat list.Count[sum:SYS Flat List.Parent].  This assumes your module is named SYS Flat List.

Flat List Module:

Calculation Module:

sagarkpr · Accepted Answer

Got it, This is how I was thinking but wondering if there is a formula I could directly use. Thanks for help