SUM data in time dimensioned module based on Boolean

Ajaykumar08ns

Hello Everyone,

I have the following scenario, could someone please help me to solve this.

I have a module dimensioned by Native time and I have 3 line items one is Boolean, Value and Target. Now I have to look at the Boolean line item if it is true then copy the current period value from value to target if its not true then we need to sum the value. Sum has to work as mentioned in the screenshot attached.

Prajjwal88

Hi @Ajaykumar08ns ,

One of the ways to achieve this is by creating a separate line item, say "False Values," to capture values where the Boolean is False. Then, in the final target line item, you can use a moving sum to get the desired output. Please refer to the screenshot below for further explanation.

(Note that for the End period number if you can define the same in some lookup module to not make it hard coded.)

I hope this helps.

Prajjwal88

Hi @Ajaykumar08ns ,

Thanks for the clarification of this problem—that really makes it an interesting logic to build.

To solve this issue, I’d recommend adding another line item, say "Distance to Next True," which will help calculate the end period value required for consecutive False inputs to be used in your moving sum formula instead of simply 99 value. This line item will identify the range between False values until the next True occurs.

Please refer to the screenshot for the formula and implementation details.

I hope this helps. 😀

Vitthal

Hi Ajay,
You can use line-Item Formulas - IF Boolean THEN Value ELSE Value + PREVIOUS (Target, 0)

• IF TRUE THEN Value ELSE Value + PREVIOUS(Target,0)
  
  Explanation: IF TRUE THEN Value: If the Boolean line item is TRUE for the current period, then the Target takes the value from the Value line item for that period. ELSE Value + PREVIOUS(Target, 0): If the Boolean line item is FALSE, then the Target takes the current period's Value and adds it to the Target value from the previous period. And the 0 for the very first period.
  
  Jan-25: Boolean is TRUE.
  Target = Value = 100
  Feb-25: Boolean is FALSE.
  Target = Value (210) + PREVIOUS(Target) (100) = 310
  Mar-25: Boolean is FALSE.
  Target = Value (220) + PREVIOUS(Target) (310) = 530
  Apr-25: Boolean is FALSE.
  Target = Value (230) + PREVIOUS(Target) (530) = 760
  May-25: Boolean is TRUE.
  Target = Value = 240
  Jun-25: Boolean is TRUE.
  Target = Value = 250.
  
  Please Community corrects me If I misunderstood.

Ajaykumar08ns

Hello @Vitthal, I think you misunderstood the issue. Answer which you provided is a very straight forward means If boolean true then pick current value else sum the current value and immediate previous value.

But what I need is I wanted to look at the boolean if boolean is true then current value else sum of all the false values.

Eg: If you see in the screenshot which I have attached I have the booleans false for Feb 25, Mar 25 and Apr 25. In Feb 25 i need to sum Feb 25+Mar 25+Apr 25, same way for Mar 25 it will be Mar 25 + Apr 25 and for Apr 25 only Apr 25 value since we dont have false booleans after Apr 25

SriNitya

@Ajaykumar08ns
Alternative approach identify start period and end period based on Booleans and use TIMESUM

Ajaykumar08ns

Hello @Prajjwal88 - It's not working as expected. What it is doing, it is summing up all the False values to the Final line item but what I need is I want to sum values only the booleans which are false in between the TRUE booleans.

Ex: If you see my screenshot below Dec 24 Final value should display only 500(Dec 24 + Jan 25) and May 25 should show 1500 (May 25 + Jun 25)

Ajaykumar08ns

Thank you all for your quick responses.

Especially @Prajjwal88 - Thank you for your response, Your solution worked for me.

Prajjwal88

@Ajaykumar08ns , Happy to help!