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.
MOVINGSUM(Line item to aggregate [, Start period] [, End period] [, Aggregation method])
Arguments
| Argument | Data Type | Description |
| Line item to aggregate (required) | Number, Boolean, date, time period, list, or text. | 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. |
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
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.
Calculation engine functionality differences
In Polaris, you can't use MOVINGSUM in formulas of line items with a formula summary method. In the Classic Engine, you can.
In Classic, you can't use Time Period as a line item argument. In Polaris, you can.
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 |
