1. Calculation functions
  2. All Functions
  3. Time and Date Functions
  4. MOVINGSUM

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

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

If omitted, MOVINGSUM aggregates all periods in the time range.

End period 

Mirrors Value to offset
data type
The end period for the aggregation.

If omitted, MOVINGSUM uses the same the 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)


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

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.

Similar functions

Detailed examples



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