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,

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.

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