The CUMULATE function calculates the sum of a given set of values either over a time period or across a specified list. By default, it calculates the sum over a time dimension. You can also include a Boolean line item to determine which values should be included in the accumulation.
Use the CUMULATE function to track progressive totals, such as cumulative sales across categories.
Syntax
CUMULATE (Values to add[, Boolean[, List]])
Arguments
| Argument | Data type | Description |
| Values to add (required) | Number | A numeric line item to cumulatively add the values of. |
| Boolean | Boolean | A Boolean line item that resets the cumulative total when TRUE. |
| List | List | See Calculation engine functionality differences. |
The CUMULATE function returns a number.
Calculation engine functionality differences
- In Polaris, you can't use CUMULATE in formulas of line items with a formula summary method. In the Classic Engine, you can.
- In Polaris, the List argument can be any list related to a dimension of the line item the formula is used on. If a different level of hierarchy is used, the argument aligns and matches with the corresponding level in the target's dimensional hierarchy.
- In Polaris, the order of a List argument follows the correct hierarchical ordering instead of the order of the list in General lists.
Constraints
Order of List argument
In Classic, if you provide the List argument, the order in which values are added follows the original order of the list in General lists, even if the list is reordered.
Time range requirements
The time range used for the Values to add argument must match the time range for the result line item.
Examples
In the example below, CUMULATE is used to cumulate the numeric values of the Sales line item over Time in months.
| Jan 12 | Feb 12 | Mar 12 | Apr12 | |
| Sales | 88,753 | 87,450 | 88,945 | 86,523 |
CUMULATE(Sales) | 88,753 | 176,203 | 265,148 | 351,671 |
The second example below uses a Boolean, Reset Sales, to reset the cumulative total for a specific month.
| Jan 12 | Feb 12 | Mar 12 | Apr 12 | |
| Sales | 88,753 | 87,450 | 88,945 | 86,523 |
CUMULATE(Sales, Reset Sales) | 88,753 | 176,203 | 88,945 | 175,468 |
| Reset Sales | true |
The third example below uses the CUMULATE function to cumulate Sales over a list, Sales Reps. Boolean is set to FALSE as no reset condition is applied.
| Edgar Harrington | Joe Tipple | Harry Boyde | Nicky Spinks | |
| Sales | 88,753 | 87,450 | 88,945 | 86,523 |
CUMULATE(Sales, FALSE, Sales Reps) | 88,753 | 176,203 | 265,148 | 351,671 |
YTD sales cumulative revenue
Calculate a running year-to-date (YTD) sales cumulative total that resets automatically when a new fiscal year begins.
Sales module
Create a Sales Performance module with these properties:
- Rows: Line items
- Monthly Revenue (Number format)
- New Year Reset (Boolean format)
- YTD Cumulative Revenue (Number format)
- Columns: Time (in months)
Enter revenue across two fiscal years in the Monthly Revenue line item.
| Nov 25 | Dec 25 | Jan 26 | Feb 26 | |
| Monthly Revenue | 10,000 | 15,000 | 12,000 | 8,000 |
New Year Reset
| ||||
YTD Cumulative Revenue
| 10,000 | 25,000 | 12,000 | 20,000 |
