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.

ℹ️ Note

Blank (empty) values are not considered as 0 values. In Pigment the average of a blank and 2 is 2 but the average of 0 and 2 is 1.

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

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

For a fixed window size of 2, the formula would be:

MOVINGSUM(SalesByEmployeeAndMonth, 2)

The output would be as in the table below. Note the following:

• The output for January is the same as the source Metric as the window size has only one data point, January, and is blank for the month prior.

• The output for February is 4 for Alice (1 for January, 3 for February, summing to 4) and 7 for Bob (2 for January, 5 for February, summing to 7).

For a fixed window size of 3, offset of 1, the formula would be:

MOVINGSUM(SalesByEmployeeAndMonth, 3, 1)

The output would be as in the table below. Note the following:

• The output for January uses data for February (due to the offset of 1) and January only. The window size is set to 3 but as the month prior to January is blank, this Item is ignored and the window size is effectively 2.

  For Alice:

  January and February sum to 4.

  For Bob:

  January and February sum to 7.

• The output for February uses January, February and March data.

  For Alice:

  January, February and March sum to 4.

  For Bob:

  January, February and March sum to 9.

For a fixed window size of 2, and the Dimension for averaging set to Employee, the formula would be:

MOVINGSUM(SalesByEmployeeAndMonth, 2, 0, Employee)

The output would be as in the table below. Note the following:

• The output for Alice is always the same as the source Metric as the window size has only one data point, Alice, and is blank for the employee prior (i.e. there is no Item in the list before Alice).

• The output for Bob in January uses data from Bob and from Alice as the window size along the Employee Dimension is 2. The source data gives 1 for Alice, and 2 for Bob, summing to 3.

For MOVINGSUM used with a dynamic window size, below is a sample dynamic window size Metric WindowEmployeeAndMonth. The data in each cell defines what the window size is for that calculation.

For a dynamic window size, the formula would be:

MOVINGSUM(SalesByEmployeeAndMonth,WindowEmployeeAndMonth)

The output would be as in the table below. Note the following:

• Alice’s window size is always 2, so for March, the source data comes from February and March. March is blank for Alice and so does not count in the calculation. February is 3, so the output is 3.

• Bob’s window size is always 3. In March, the source data comes from January, February and March. These sum to 9.

See also: MOVINGAVERAGE function