MOVINGAVERAGE function

  • 14 January 2022
  • 2 replies
  • 169 views

Userlevel 1

 

Description

 

Computes the average of numbers within a fixed-size 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.

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

 

Syntax

MOVINGAVERAGE(input_block, window_size [, end_offset] [, dimension])
  • input_block is the data source on which moving average is computed, and must be an expression of integer or decimal type. 
  • window_size is the size of the moving window (number of items considered). Must be above or equal to 1. 
  • end_offset is the offset of the last item within the window relative to current one. This argument defaults to zero, meaning the window includes all items from window_size - 1 to current one. Default: 0.
  • 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. Default: a Time Dimension on which input_block is defined (Day, Month, Quarter or Year).

 

Return type

Same as source, either Integer or Number.

 

Example

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

  Jan Feb Mar Apr May
Alice 1 3   2 8
Bob 2 5 2 1  

 

 

Formula = MOVINGAVERAGE(SalesByEmployeeAndMonth, 2):

  Jan Feb Mar Apr May
Alice 1 2 3 2 5
Bob 2 3.5 3.5 1.5 1

 

 

Formula = MOVINGAVERAGE(SalesByEmployeeAndMonth, 3, 1):

  Jan Feb Mar Apr May
Alice 2 2 2.5 5 5
Bob 3.5 3 2.67 1.5 1

 

 

Formula = MOVINGAVERAGE(SalesByEmployeeAndMonth, 2, 0, Employee):

  Jan Feb Mar Apr May
Alice 1 3   2 8
Bob 1.5 4 2 1.5 8

 

 

Note on Blank values: Blank (empty) values are not considered as 0 values ; In Pigment the average of Blank and 2 is 2 but the average of 0 and 2 is 1.

 

See also

 


2 replies

Userlevel 2
Badge +1

Why does Bob-Mar in this example MOVINGAVERAGE(SalesByEmployeeAndMonth, 3, 1) = 3?

 

Shouldn’t the moving average equate to 8/3?

Userlevel 1

Why does Bob-Mar in this example MOVINGAVERAGE(SalesByEmployeeAndMonth, 3, 1) = 3?

 

Shouldn’t the moving average equate to 8/3?

Hi MKohli, good catch! You’re right indeed, the sample didn’t match actual results and was just updated. Thanks a lot for reporting it!

Reply