Sum based on Parent in Hierarchical List

shubhamrawat
shubhamrawat
in Modeling

Hi,

I have a hierarchy of lists, L1>L2>L3. My module is dimentioned by L3 and has two line items, Input value and Input %. Input Value is entered by user. Input % = Input Value/Sum of Input Values for all list items of L3 under a L2 list item.

How can I calculate this sum, as it is the sum of all items in L3 under L2 a list item. Please let me know, thanks.

Tagged:

Best Answer

  • AjayM
    AjayM
    edited March 2023 Answer ✓

    Hi @shubhamrawat ,

    Basically you need sum of input values at L2 level for the denominator portion. Hence you would use a LOOKUP here to fetch L2 level total, to L3 level.

    If you have a SYS L3 module for L3 dimension, where you identify L2 parent for each L3 item (as in, L2 parent=parent(item('L3'))), you can use this lineitem in Input% formula, as Input Value/Input Value[Lookup:SYS L3.L2 Parent]. You will need to have summary for input value set to SUM for this approach to work.

    Hope this helps.

    Cheers!

Answers

Categories