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

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.

rob_marshall
Moderator

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

@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

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,

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

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

taku.s
Contributor

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

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

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.

ChrisHeathcote
Community Boss

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

@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 Heathcote
Bedford Consulting

Gold Partner and Regional Partner of the Year 2021, EMEA
MarkWarren
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...