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?
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:
Got it, This is how I was thinking but wondering if there is a formula I could directly use. Thanks for help