How to calculate the cumulative value without using the CUMULATE function

taku.s

Hi everyone.

I would like to know how to calculate the cumulative value without using the CUMULATE function.

Please refer to the Excel diagram.

I would like to put FORMULA in the LINEITEM of CUMULATE.

I would like to avoid using the CUMULATE function if possible, as it degrades performance.

Best regards,

Accepted answers

All comments

AntonMineev

Hi @taku.s!

If you use native time, than you can try use PREVIOUS(Line Item) + Line Item. This is more performance variant.

If you are using a different list/custom time, you can switch/map to the native time and use the formula above. And then transfer the result back. It helped us.

rob_marshall

@taku.s

if that is your list, the 1 though 10, simply define a Top Level and the system will automatically aggregate the values.

Rob

taku.s

Hi, @AntonMineev

I didn't explain well enough.

The Numbered List is applied to this module, so the Time Scale is not used.

Therefore, I think that the PREVIOUS function cannot be used.

Best regards,

AntonMineev

Use native time as an interlayer. It doesn't make business sense, just a technical solution.

Снимок экрана 2021-05-21 в 16.33.58.png

taku.s

Hi, @AntonMineev

Thank you for your reply.

The suggestions you provided were very helpful.

What should I do if I have a large amount of data?

For example, if I have more than 1 million rows of data.

Best regards,

AntonMineev

You can try to optimize the size, but in any case, this calculation requires space.

The simplest tips:
1. Disable summation in intermediate calculation (read Planual 2.03-01 Turn Summary options off by default).

2. If several dimensions are involved in the calculation, make a crossed technical hierarchy to reduce sparsity.

ChrisAHeathcote

@taku.s

The use of RANKCUMULATE may also be an option.

=RANKCUMULATE(Value,1)

This function acts like CUMULATE on time but for a combination of dimensions.

But I suspect this has a similar impact on performance as CUMULATE so may not be the correct solution for you.

MarkWarren

You're right that RANKCUMULATE does have performance impact.
It is a function that has to run single-threaded, most other Anaplan functions can the benefit of splitting calculations over multiple parallel CPU threads to improve speed; this function doesn't, neither does RANK or ISFIRSTOCCURENCE.

CUMULATE when used over a list will also run single-threaded and this may have been the concerns of @taku.s in the original post.
CUMULATE over time does not have that problem, so those solutions here will perform better (than using with a list).

However, I think you missed Rob's point that a if you just need the cumulative total over that list, use a summary.
Another similar alternative would be to change the boolean to list formatted and have a list with 2 items, "TRUE" and "FALSE"; you can then do a SUM...

Quick Links

Can you help?

Unanswered questions

Where can I find export file after running export process using machine task in a workflow?
I am testing export actions using workflow but its not generating any file when I am running the workflow, but the same process is generating export file when ran from the Actions→Process section. Is their any issue with the workflow functionality? Also, If I want to generate a file after completion of workflow and pull…
Automating Flat File Uploads to Anaplan Data Orchestrator (ADO) – Best Practices and Methods
Hello Anaplan Community, I am seeking guidance on the most effective ways to automatically upload flat files to Anaplan Data Orchestrator (ADO). Currently, our process involves loading flat files to an Anaplan Datahub using Anaplan APIs and custom Python scripting. With Anaplan Data Orchestrator's capabilities for data…
How to Design Simulation Planning in Anaplan with Multi-Level User Input and Step-by-Step Logging?
Hi Anaplan Community, I'm working on a simulation planning model where users need the flexibility to input pricing adjustments at any level of the product hierarchy, and I’d love your guidance on best practices. 📌 Scenario Overview: We have a 4-level product hierarchy: Brand Category Sub Category SKU (lowest level) The…