SUM data in time dimensioned module based on Boolean

Ajaykumar08ns · 2025-02-05T22:06:00+00:00

There was an error rendering this rich post.

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.

Accepted answers

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. 😀

All comments

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

Prajjwal88

(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.

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)

Prajjwal88

Hi @Ajaykumar08ns ,

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

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

I hope this helps. 😀

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!

Quick Links

Can you help?

Unanswered questions

Request for Workflow Design: End-User Report Finalization & Stakeholder Notification with Version Co
Hi everyone, I'm looking to design a workflow and would appreciate your input or guidance on how to set this up effectively in Anaplan. So, the goal is: Once end users complete their data input, they should be able to trigger a process that: Notifies stakeholders via email with a link to a report. The report includes: A…
Generating Failure Dump - even though no warnings/error in Actions
I've few actions in our Supply model which when run takes some time on the "generating failure dump" step at the end of the action even though the import is a full success without warning or errors. This is just a few seconds when run for a single action, however we have a process with 120 actions, and most of these…
OAUTH 2.0 vs Google Sheets Extension
Hi, My org is looking to explore leveraging Google Apps script to extract data out of Anaplan and update already built templates in Google Sheets. We use the Anaplan Extension currently but find it crashes often with the amount of tabs, data, etc. Has anyone used both Anaplan OAUTH 2.0 API and Goolge Apps Script to write…