How to calculate the cumulative value without using the CUMULATE function Contributor

How to calculate the cumulative value without using the CUMULATE function

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,

8 REPLIES 8   Certified Master Anaplanner

Re: How to calculate the cumulative value without using the CUMULATE function

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

Re: How to calculate the cumulative value without using the CUMULATE function

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

Rob Contributor

Re: How to calculate the cumulative value without using the CUMULATE function

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,   Certified Master Anaplanner

Re: How to calculate the cumulative value without using the CUMULATE function

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

Re: How to calculate the cumulative value without using the CUMULATE function

Hi, @AntonMineev

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,   Certified Master Anaplanner

Re: How to calculate the cumulative value without using the CUMULATE function

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.   Community Boss

Re: How to calculate the cumulative value without using the CUMULATE function

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.

Chris
HeathcoteAndHerran.com   Expert

Re: How to calculate the cumulative value without using the CUMULATE function

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