Active child under a Parent - Check only future periods
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?
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.