MOVINGSUM function

Description

Computes the sum of numbers within a window moving along a Dimension List. For each Item from a given Dimension List, the sum is computed from all defined numbers within the considered window. Blank values are ignored.

The window size can be either:

• fixed - the syntax defines how many Items are in the window

• dynamic - the syntax contains an expression that defines window size for each Item

By default, if the input Block is defined over a Time Dimension, the moving sum is applied over this Dimension.

Syntax

MOVINGSUM(Input, Window Size [, End Offset] [, Dimension])

• Input is the data source on which the moving sum is computed. It must be an expression with a data type of Integer or Number.

• Window Size is the size of the moving window (number of items considered). It can be an integer above or equal to 1, or an expression comprising a Metric or Metric with calculations. Metrics used for this must have a data type of Integer and comprise Dimensions used by the Input.

• End Offset is the offset of the last item within the window relative to current one. This also can be an integer or a Metric. Metrics used for this must have a data type of Integer and comprise Dimensions used by the Input.
  It defaults to zero, meaning the window includes all Items from Window Size - 1 to the current Item.

• Dimension is the Dimension List along which the window is moving. This argument defaults to the Time Dimension of the Input if there is exactly one, and must be explicitly specified otherwise.

TIP

If you do not need a variable window size, use an integer. It simplifies the computation and speeds up performance.

Return type

Same as Input: either Integer or Number.

Example

Metric SalesByEmployeeAndMonth defined on two Dimensions (Month and Employee):

Window size Metric WindowEmployeeAndMonth:

Output with window size of 2:

MOVINGSUM(SalesByEmployeeAndMonth, 2)

Output with window size of 3, offset of 1:

MOVINGSUM(SalesByEmployeeAndMonth, 3, 1):

Output with window size of 2, Dimension of Employee:

MOVINGSUM(SalesByEmployeeAndMonth, 2, 0, Employee)

Output with dynamic window size provided by Metric WindowEmployeeAndMonth:

MOVINGSUM(SalesByEmployeeAndMonth,WindowEmployeeAndMonth)

Output with dynamic window size, Dimension of Employee:

MOVINGSUM(SalesByEmployeeAndMonth,WindowEmployeeAndMonth,Employee)

See also: MOVINGAVERAGE function