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])

ArgumentData TypeDescription
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.

 MOVINGSUM(Bonus pay, -2, 0)


JanFebMarAprilMayJuneJulyAug
Bonus pay6,1505,8505,9006,0254,8504,9505,6006,250

MOVINGSUM
(Bonus pay, -2, 0)

6,150

12,000

17,900

17,775

16,775

15,825

15,400

16,800

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.
  • 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.

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.


JanFebMarAprilMayJuneJulyAug
Line item3,0002,3502,0002,2002,5003,0004,5003,700
MOVINGSUM
(Line item, -2, 0, AVERAGE)
3,0002,6752,4502,1832,2332,5673,3333,733
MOVINGSUM
(Line item, -2, 0, MAX)
3,0003,0003,0002,3502,500 3,0004,5004,500
MOVINGSUM(x, -2, 0, MIN)3,0002,3502,0002,0002,0002,2002,5003,000
MOVINGSUM(x, -3, 0, MIN)6,0005,3505,0004,3504,5005,0006,7007,000