Moving average 3 months in script

[cheryl]

HI Community,

I have a table like this:

Load
Month,
Avg([Results]) as Results
resident Survey_results
where "Respondent Unit"='SL'
group by Month;

This will give a number every month; e.g.

Jan-22: 3;

Feb-22: 4;

Mar-22: 5;

Apr-22: 3.5;

I would like to create another table which shows the moving average of 3 months, which is (this month + previous 2 months)/3.

So the new table will look like:

Mar-22 : (3+4+5)/3 = 4

Apr-22 : (4+5+3.5)/3 = 4.167

How should I write script in Qliksense that can give me such table?

Thanks!

Cheryl

[QFabian · Accepted solution]

Hi @cheryl , please try something like this, using inter record function 'previous' or peek. 

Check both for your understanding.

https://help.qlik.com/en-US/cloud-services/Subsystems/Hub/Content/Sense_Hub/ChartFunctions/InterReco...

Data:
Load
Month,
Avg([Results]) as Results
resident Survey_results
where
"Respondent Unit"='SL'
group by
Month;

Data_3:
Load
Month,
(Results + previous(Result) + previous(previous(Result))) / 3 s Result_Mov1
(Results + peek(Result) + peek(Result, 2)) / 3 s Result_Mov2
Resident Data
order by
Month;