MOVINGAVERAGE function

  • 14 January 2022
  • 2 replies
  • 1302 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, Window Size [, End Offset] [, Dimension])
  • Input - is the data source on which the moving average is computed, can be a Metric or List with an Integer or Number 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 Source 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

 


This topic has been closed for comments

2 replies

Userlevel 4
Badge +8

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!