Moving Average not working

[tracycrown]

Dear all

Kindly advise why 4 quarters moving average MA(4) is not working after adding year and quarter in the dimension ?.

The correct answer should be as follows: 

t	Year	Quarter	Sales	MA(4)
1	2014	1	4.8
2	2014	2	4.1
3	2014	3	6.0	5.4
4	2014	4	6.5	5.6
5	2015	1	5.8	5.9
6	2015	2	5.2	6.1
7	2015	3	6.8	6.3
8	2015	4	7.4	6.4
9	2016	1	6.0	6.5
10	2016	2	5.6	6.6
11	2016	3	7.5	6.7
12	2016	4	7.8	6.8
13	2017	1	6.3	6.9
14	2017	2	5.9	7.0
15	2017	3	8.0	7.2
16	2017	4	8.4

Thank you

Tracy

[sunny_talwar · Accepted solution]

Try this

Aggr(RangeAvg(Above(Sum(Sales), 0, 4)), t)

[tracycrown]

Dear Sunny

The answer is correct but how to make first two (t1 & t2) and last (t16) records of MA(4) equal blank ?.

Thank you

Tracy

[sunny_talwar]

Try this

Aggr(If(RowNo() > 2 and RowNo() <> NoOfRows(), RangeAvg(Above(Sum(Sales), 0, 4))), t)