Description

Computes the average of numbers within a window moving along a Dimension List. For each Item from a given Dimension List, the average 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 average is applied over this Dimension.

Syntax

MOVINGAVERAGE(Input, Window Size z, End Offset] t, 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.

Return type

Number 

Example

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

Window size Metric WindowEmployeeAndMonth:

Output with window size of 2:

MOVINGAVERAGE(SalesByEmployeeAndMonth, 2)

Output with window size of 3, offset of 1:

MOVINGAVERAGE(SalesByEmployeeAndMonth, 3, 1):

Output with window size of 2, Dimension of Employee:

 MOVINGAVERAGE(SalesByEmployeeAndMonth, 2, 0, Employee)

Output with dynamic window size provided by Metric WindowEmployeeAndMonth:

MOVINGAVERAGE(SalesByEmployeeAndMonth,WindowEmployeeAndMonth)

Output with dynamic window size, Dimension of Employee:

MOVINGAVERAGE(SalesByEmployeeAndMonth,WindowEmployeeAndMonth,Employee)

Note on Blank values: 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.

See also: