Convert CUMULATE functions to PREVIOUS | Model Optimization Team series
Hello! Welcome to the Model Optimization team's monthly blog post. This is an eight-part series, following the topics we see occurring most frequently in slow customer models. Our aim is to inform and guide how to build with best practice intentions for these model building topics.
Each month, two of our analysts will take you through how to spot and improve these formula structures! We hope these can help with your model performance.
All the best - Cianna Bramley (Model Optimization Manager)
……
This month's blog is brought to you by Kenneth Privaldos and Mai Hashad.
Efficient calculations are key to optimal performance in Anaplan models. While CUMULATE is commonly used for aggregation, it can slow down models, especially with large datasets. A better alternative is the PREVIOUS function. This blog explains why switching from CUMULATE to PREVIOUS improves performance and provides a step-by-step guide on how to make the change.
Why is this bad?
The CUMULATE function in Anaplan is often used to sum values across time periods or lists. While it can be effective for simple calculations, it can also cause significant performance issues when dealing with larger datasets or complex models. Here's why…
Cumulate across time period
CUMULATE() across a time period needs to reference all the preceding periods for each calculation but, dealing with calculations that span over many time periods can cause poor performance. This is where the PREVIOUS() function can be used as an alternative. Planal 2.02-10 Using PREVIOUS() vs CUMULATE() recommends using previous for a long timescale. For shorter time scale, you should still use the CUMULATE() function. Preferably a monthly time period of 1-2 years.
While CUMULATE() needs to reference all the preceding periods, the PREVIOUS() function only needs to reference the immediately preceding period (only looking one step back).
Variations of CUMULATE to watch out for:
- YEARTODATE()
- HALFYEARTODATE()
- QUARTERTODATE()
- MONTHTODATE()
- WEEKTODATE()
Cumulate across list
When CUMULATE is used over a list (e.g., CUMULATE(x, y, z)), performance can suffer, especially with datasets that exceed a few million cells. This is because CUMULATE operates on a single calculation thread, leading to delays when processing high cell counts. For more detailed guidance see OEG Best Practice: Performance issues with CUMULATE over lists.
This is because CUMULATE operates on a single calculation thread.
How do we find it?
There are 3 methods to find CUMULATE function in your model:
- Manual inspection
- MAPS
- Model Optimization Reports
For manual inspection, you can search the formula of the module and look for a line item using the CUMULATE function. Furthermore, you can download the line item blueprints and search for CUMULATE in your csv file.
You can also request for MAPS or (Model Analysis Pro-Active Solution) which can provide Planual infractions, but this is not available for all models.
Lastly, when in a Model Optimization report, we check some Planual infractions such as CUMULATE as part of our analysis in the best practice sections.
How do we improve it?
Example 1: Converting CUMULATE (line item, BOOLEAN) to PREVIOUS
Scenario: You are using CUMULATE function to aggregate sales over a period of 84 months. This can lead to performance issues, especially with larger datasets. Instead, you can use PREVIOUS for more efficient calculation.
Data:
Current setup:
Using the PREVIOUS function:
This formula checks if Include? is TRUE. If so, it adds Sales to the cumulative total; otherwise, it continues with the previous value.
Example 2: Converting YEARTODATE(Line Item) to PREVIOUS
Scenario: You are using YEARTODATE function to calculate cumulative sales from the start of the year, and like CUMULATE, this can cause performance lags when working with larger models.
Current setup:
To use the PREVIOUS() function, we need to determine the starting month of the year that is set in the time settings. You can do this by checking the time settings of the model as shown below.
In a SYS Time module, create a Boolean line item and copy the formula as shown below. Since the fiscal year start month is set to January, we can set the formula equal to one. Otherwise, if it starts in another month, set the month number to whatever the start month is, e.g. If their year starts in March, it would be MONTH(ITEM(Time)) = 3.
Using the PREVIOUS function:
This formula checks if first month of each year is TRUE. If so, the value will become zero; otherwise, it continues with the previous value.
Example 3: Converting CUMULATE (line item, FALSE, List: [Custom Time])
Scenario: You are using CUMULATE with a fake time list which can lead to a slow calculation especially when the line item operates over a few million cells, because the calculations run on a single thread.
For best results, restructure this formula to use PREVIOUS. Refer to OEG Best Practice: Performance issues with CUMULATE over lists.
Conclusion
Switching from CUMULATE and similar functions (e.g., YEARTODATE, MONTHTODATE) to PREVIOUS can yield significant performance benefits. By streamlining these calculations, you can reduce processing time, optimize system efficiency, and improve the overall user experience within Anaplan.
……….
Check out the other articles in the series:
Comments
-
Hi @modeloptimizationteam ,
A while ago @rob_marshall told me that Movingsum() performs better than lag or next even when retrieving only one number instead of multiple. Thinking about this article how does performance of previous() compare to movingsum() if previous time periods value should be retrieved?0 -
Previous() only goes back one time period while movingsum() has parameters to go back/forward X amount time periods.
0 -
@rob_marshall That part is clear but interested in performance difference if you compare Previous(line item) vs. Movingsum(line item, -1,-1)
0 -
I don't think we ever tested movingsum() vs. previous()…we did test Previous vs Lag vs Lookup.
0
