How to calculate the cumulative value without using the CUMULATE function

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

capt.jpg

 

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

Best regards,

8 REPLIES 8
AntonMineev
Certified Master Anaplanner

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
Moderator

@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
Contributor

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

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
Contributor

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

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

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

Chris
HeathcoteAndHerran.com
MarkWarren
Expert

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