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.

MOVINGSUM(Line item to aggregate [, Start offset] [, End offset] [, Aggregation method])

ArgumentData typeDescription
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
data type

The ending value for the aggregation.

When you omit this argument, it defaults to the same value as the Start offset.

Aggregation methodKeyword

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.

KeywordData typePurpose
SUMNumberAdds the input values
AVERAGENumberCalculates the average of the input values
MINNumber, dateIdentifies the minimum input value
MAXNumber, dateIdentifies the maximum input value
ANYBooleanChecks if any input value is TRUE/FALSE
ALLBooleanCheck if all input values are TRUE/FALSE
FIRSTNONBLANKDate, list, textReturns the first non-blank input value
LASTNONBLANKDate, list, textReturns the last non-blank input value
TEXTLISTTextConcatenates 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.
BehaviorPolarisClassic
Two-argument variantMOVINGSUM(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 rangesCan 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 timescalesCan'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 argumentsWhen 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 behaviorWhen 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 datesWhen 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 methodsFIRSTNONBLANK, LASTNONBLANK, and TEXTLIST aren't available.All aggregation methods are available.

 MOVINGSUM(Bonus, -2, 0)


Jan 22Feb 22Mar 22Apr 22May 22Jun 22Jul 22Aug 22
Bonus6,1505,8505,9006,0254,8504,9505,6006,250
MOVINGSUM
(Bonus, -2, 0)

6,150

12,000

17,900

17,775

16,775

15,825

15,400

16,800
  • 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.

Jan 22Feb 22Mar 22Apr 22May 22Jun 22Jul 22Aug 22
X200250300500400450200400
MOVINGSUM
(X, -2, 0, AVERAGE)
200225250350400450350350
MOVINGSUM
(X, -2, 0, MAX)
200250300500500500450450
MOVINGSUM(X, -2, 0, MIN)200200200250300400200200
MOVINGSUM(X, -3, 0, MIN)200200200200250300200200