Active child under a Parent - Check only future periods

GuruAP
GuruAP
in Modeling

Hi - I have a hierarchy of two levels - L1 (Parent) and L2 (Child). I have a module with the L2 dimension where a boolean line item is input across time periods (past and future). As we roll into the future, only the future x number of periods (say, 8 ) is updated to check which child members are active under a parent.

My requirement is to check the active L1 list member only if at least one L2 member under the L1 member is active in any of the future 8 periods. If no L2 member is active in the future 8 periods, the L1 member is not considered as ACTIVE. I have used a count line item for active boolean periods and no time scale line item to check the total count of L2 members under a L1. In the line item with no time scale, I have used the formula Count Boolean[LOOKUP: 'L1', SELECT: TIME.All Periods]. But here, I want to check the TIME only for 8 periods in the future. How do I do that?

Thanks,

Regards - Guru

Tagged:

Best Answer

  • saurabh.raheja
    saurabh.raheja
    Answer ✓

    Hi @GuruAP 

     

    One way I think this can be done is to use the Timesum on the 'Count' line item. In Timesum function, you can specify the Start Period and End Period for your calculation. But first you need to calculate count for the L1 list since Timesum can't be used in conjunction with another function like Lookup.

    - Count L2: Assuming we already have 'Count L2' line item which counts the number of Active L2 members based on the boolean check. 

    Count L1: Add 'Count L1' using L1 list as a dimension and simple refer this to 'Count L2'. This gives us the Count at L1.

    Timesum: Add a line item 'Timesum' and write the following formula Timesum('Count L1', Start Period, End Period)

    *Start period and End Period should be defined in Timeperiod formatted line items either manually or using a formula if possible to make this calculation more dynamic.

     

    Regards

    Saurabh

Answers

  • einas.ibrahim
    einas.ibrahim

    Hi @GuruAP 

     

    Have you tried to change the summary method of the boolean to "Any"? This means the value of the parent will automatically be TRUE if any of the children's values are true (one or more)

     

    That contrasts with the summary method "All", which will be TRUE for the parent if every child under that parent has the value as TRUE.

  • Kanishq17
    Kanishq17

    Hi,

     

    you can take parent L1 for the L2 item and aggregate on basis of ANY , for any active L2 in the period, the L1 will be ticked as positive,

     

    So in target module for L1 parent, you can do bool[any:L1 parent] 

     

Categories