The MOVINGSUM function returns the sum of values over a changing time range. As the time range moves through the data, it aggregates the sum for the updated time range.
Use this function to create rolling summaries or forecasts based on a series of data, such as weekly sales or performance results.
Syntax
MOVINGSUM(Line item to aggregate [, Start offset] [, End offset] [, Aggregation method])
Arguments
| Argument | Data type | Description |
| Line item to aggregate (required) | Number, boolean, date, list, or text | The reference value that you want to aggregate over a period of time. This argument must be a line item. |
| Start offset | Number | The starting value for the aggregation. When you omit this argument, it considers the entire range of the time period for the calculation. |
| End offset | Number Mirrors Start offset | The ending value for the aggregation. When you omit this argument, it defaults to the same value as the Start offset. |
| Aggregation method | Keyword | The aggregation method to use. The available keywords are SUM, AVERAGE, MIN, MAX, ANY, ALL, FIRSTNONBLANK, LASTNONBLANK, and TEXTLIST. There's more information in the Aggregation method argument section below. |
The MOVINGSUM function returns a value that matches the data type of the Line item to aggregate argument.
Aggregation method argument
| Keyword | Data type | Purpose |
| SUM | Number | Adds the input values |
| AVERAGE | Number | Calculates the average of the input values |
| MIN | Number, date | Identifies the minimum input value |
| MAX | Number, date | Identifies the maximum input value |
| ANY | Boolean | Checks if any input value is TRUE/FALSE |
| ALL | Boolean | Check if all input values are TRUE/FALSE |
| FIRSTNONBLANK | Date, list, text | Returns the first non-blank input value |
| LASTNONBLANK | Date, list, text | Returns the last non-blank input value |
| TEXTLIST | Text | Concatenates the input values with a " , " separator to create a single text value |
When you omit the Aggregation method argument, the default behavior depends on the data type of the Line item to aggregate argument:
- If the data type of the Line item to aggregate is a number, the default method is SUM.
- If the data type of the Line item to aggregate is boolean, the default method is ANY.
- If the data type of the Line item to aggregate is a date, list, or text, the default method is FIRSTNONBLANK.
Calculation engine functionality differences
| Behavior | Polaris | Classic |
| Two-argument variant | MOVINGSUM(source, start) aggregates the source values from the point corresponding to the start offset to the end of the time dimension. | MOVINGSUM returns the value of the source at the point corresponding to the start offset, which is equivalent to OFFSET(source, start, 0). |
| Reference across time ranges | Can reference different time ranges. For example, MOVINGSUM can reference a Line item to aggregate with a time dimension based on a different time range than the time dimension of the target line item. | Can't reference different time ranges. |
| Reference across timescales | Can't refer to a source line item with a time dimension at a coarser timescale than the time dimension of the target. | Always possible to refer to a source line item with a coarser time dimension. |
| NaN handling in arguments | When either the Start offset or End offset argument is NaN, MOVINGSUM returns the default value for the type of Line item to aggregate. | NaN in either argument is treated as the offset corresponding to the earliest period in the time dimension. |
| Empty aggregation behavior | When there are no values to aggregate, that is when the Start offset is greater than the End offset, MOVINGSUM returns the default value for the type of Line item to aggregate. The default values include 0 for numbers, False for Boolean, and blank for other data types. | Returns value depending on the combination of the aggregation method used and its data type. |
| Blank handling in MIN aggregation method on dates | When comparing blank with a non-blank date value, MIN ignores blank and returns the non-blank value. When all values are blank, it returns blank. | When blank is compared with a non-blank date value, it returns blank. |
| Unavailable aggregation methods | FIRSTNONBLANK, LASTNONBLANK, and TEXTLIST aren't available. | All aggregation methods are available. |
Syntax example
MOVINGSUM(Bonus, -2, 0)
| Jan 22 | Feb 22 | Mar 22 | Apr 22 | May 22 | Jun 22 | Jul 22 | Aug 22 | |
| Bonus | 6,150 | 5,850 | 5,900 | 6,025 | 4,850 | 4,950 | 5,600 | 6,250 |
MOVINGSUM(Bonus, -2, 0) | 6,150 | 12,000 | 17,900 | 17,775 | 16,775 | 15,825 | 15,400 | 16,800 |
Constraints
- The result line item must be the same data type as the Line item to aggregate argument.
- If you use a decimal number for the Start offset or End offset arguments, MOVINGSUM rounds it to the nearest integer.
- If you use an expression for the Line item to aggregate argument that contains another function, that function must use the same Time Range as the result line item.
- If the Start offset or End offset extend beyond the module's Time Range, values outside the range are ignored in the aggregation.
Examples
| Jan 22 | Feb 22 | Mar 22 | Apr 22 | May 22 | Jun 22 | Jul 22 | Aug 22 | |
| X | 200 | 250 | 300 | 500 | 400 | 450 | 200 | 400 |
MOVINGSUM | 200 | 225 | 250 | 350 | 400 | 450 | 350 | 350 |
MOVINGSUM | 200 | 250 | 300 | 500 | 500 | 500 | 450 | 450 |
MOVINGSUM(X, -2, 0, MIN) | 200 | 200 | 200 | 250 | 300 | 400 | 200 | 200 |
MOVINGSUM(X, -3, 0, MIN) | 200 | 200 | 200 | 200 | 250 | 300 | 200 | 200 |
