MOVINGAVERAGE function

Description

Syntax

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

• Input - is the data source on which the moving average is computed. It can be a Metric or List 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 an 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.
  This setting can be used to either look into past or future values. For example, if you wish to see the moving formula for 12 items into the future on a span of three items, your formula should include this parameter: MOVINGAVERAGE(metric, 3, 12)

• Dimension - is the Dimension List along which the window is moving. This argument defaults to the Time Dimension of 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

Number

Example

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

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

MOVINGAVERAGE(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 2 for Alice (1 for January, 3 for February, summing to 4, all divided by 2 as the window size is 2) and 3.5 for Bob (2 for January, 5 for February, summing to 7, all divided by 2).

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

MOVINGAVERAGE(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. Dividing by 2 to produce the average yields 2.

For Bob:

• January and February sum to 7. Dividing by 2 yields 3.5.

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

For Alice:

• January, February and March sum to 4. While the window size is set to 3, March is blank for Alice, meaning that this Item is ignored and the window size is effectively 2. Dividing by 2 to produce the average yields 2.

For Bob:

• January, February and March sum to 9. Dividing by 3 yields 3.

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

MOVINGAVERAGE(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. Dividing by 2 to produce the average yields 1.5.

For MOVINGAVERAGE 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:

MOVINGAVERAGE(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. Dividing all by 3 also yields 3.

See also: MOVINGSUM function