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,

Answers

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

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

  • 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,

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

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

  • 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,

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

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

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