Skip to Content
0
Jul 09, 2020 at 07:58 PM

Calculating n-days moving/rotating average

32 Views

I need to calculate the n-day moving/rotating average. Let's say n=3. See the sample table data below. How can I do this with BEx?

Please note that it is important to include the day without quantity when the last 3-day average is calculated. See example for calculating a 3-day average for 1/11/2020 and 1/12/2020.

Date         Qty    Qty 3-day Ave      [Calculations]
1/1/2020     100        100            [100/1]
1/2/2020     200        150            [200+100)/2]
1/3/2020     300        200            [(300+200+100)/3]
1/4/2020     400        300            [(400+300+200)/3]
1/5/2020     500        400            [(500+400+300)/3]
1/6/2020     400        433            [(400+500+400)/3]
1/7/2020     300        400            [(300+400+500)/3]
1/8/2020     200        300            [(200+300+400)/3]
1/9/2020     100        200            [(100+200+300)/3]
1/10/2020    200        167            [(200+100+200)/3]
1/11/2020               100            [( 0 +200+100)/3]
1/12/2020    400        200            [(400+ 0 +200)/3]

<br>