MOVINGSUM returns values over a changing time range. For each time range included, it aggregates the sum of values found.
You could use MOVINGSUM to create a rolling, aggregated forecast based on weekly sales results.
Syntax
MOVINGSUM(Line item to aggregate [, Start period] [, End period] [, Aggregation method])
Arguments
| Argument | Data Type | Description |
| Line item to aggregate (required) | Number, Boolean, date, list, or text This argument must be a line item. | The line item to aggregate over a period of time. |
Start period | Number | The start period for the calculation. If omitted, MOVINGSUM aggregates all periods in the time range. |
End period | Mirrors Value to offset data type | The end period for the calculation. If omitted, MOVINGSUM uses the same value from the start period. |
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 below. The default behavior, if omitted, depends upon the data type of the Line item. |
Calculation engine functionality differences
In Polaris, MOVINGSUM(source, start) aggregates the source values from the point corresponding to the start offset to the end of the time dimension. In Classic, this variant just returns the value of source at the point corresponding to the start offset, i.e. it is equivalent to OFFSET(source, start, 0).
In Polaris, MOVINGSUM can make references across different time ranges. For example, it can reference a source line item with a time dimension based on a different time range from the time dimension of the target line item. This isn't possible in Classic.
In Polaris, MOVINGSUM can't refer to a source line item with a time dimension at a coarser timescale than that of the time dimension of the target. In Classic, this is always possible.
In Polaris, when either the Start period or End period argument for this function is NaN, it returns the default value. In Classic, a NaN in either argument is treated as if it was the offset corresponding to the earliest period in the time dimension.
In Polaris, when you use the MIN aggregation method, MOVINGSUM ignores blank values. In Classic, blank is the minimum value for the date data type, so MIN always returns blank when one of the dates involved in the aggregation is blank.
In Polaris, some of the aggregation methods are not available, namely: FIRSTNONBLANK, LASTNONBLANK, and TEXTLIST.
Syntax example
MOVINGSUM(Bonus pay, -2, 0)
| Jan | Feb | Mar | April | May | June | July | Aug | |
| Bonus pay | 6,150 | 5,850 | 5,900 | 6,025 | 4,850 | 4,950 | 5,600 | 6,250 |
MOVINGSUM(Bonus pay, -2, 0) | 6,150 | 12,000 | 17,900 | 17,775 | 16,775 | 15,825 | 15,400 | 16,800 |
Additional information
Supported aggregation methods
Each aggregation method supports specific data types, namely:
| Aggregation method | Supported data type(s) |
| SUM | Number |
| AVERAGE | Number |
| MIN | Number, Date |
| MAX | Number, Date |
| ANY | Boolean |
| ALL | Boolean |
| FIRSTNONBLANK | Date, List, Text |
| LASTNONBLANK | Date, List, Text |
| TEXTLIST | Text |
Default aggregation method
The default aggregation method used when you omit the Aggregation method argument varies based on the data type of the Line item to aggregate argument. If the Line item to aggregate argument is:
- Number-formatted, the default behavior is to SUM.
- Boolean-formatted, the default behavior is ANY.
- Date-, list-, or text-formatted, the default behavior is FIRSTNONBLANK.
Constraints
- You can only provide a latter argument if you provide all prior arguments.
- The result line item must be the same format as the Line item to aggregate argument.
- If you use a decimal number for the Start period or End period arguments, it rounds 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 period or End period arguments specify periods outside the module's Time Range, they're omitted from the aggregation.
Examples
| Jan | Feb | Mar | April | May | June | July | Aug | |
| Line item | 3,000 | 2,350 | 2,000 | 2,200 | 2,500 | 3,000 | 4,500 | 3,700 |
MOVINGSUM | 3,000 | 2,675 | 2,450 | 2,183 | 2,233 | 2,567 | 3,333 | 3,733 |
MOVINGSUM | 3,000 | 3,000 | 3,000 | 2,350 | 2,500 | 3,000 | 4,500 | 4,500 |
MOVINGSUM(x, -2, 0, MIN) | 3,000 | 2,350 | 2,000 | 2,000 | 2,000 | 2,200 | 2,500 | 3,000 |
MOVINGSUM(x, -3, 0, MIN) | 6,000 | 5,350 | 5,000 | 4,350 | 4,500 | 5,000 | 6,700 | 7,000 |
