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

sagarkpr

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

@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: