Qlik Community

QlikView Documents

QlikView documentation and resources.

Announcements
Document boards are being consolidated, this board no longer allows NEW documents READ MORE

N-period rolling data, moving average, easy solution.

cancel
Showing results for 
Search instead for 
Did you mean: 
hubertdudek
Contributor III
Contributor III

N-period rolling data, moving average, easy solution.

Attachments

I found a lot of posts in qlikView community saying how hard is to make rolling data. Some of them even say that it's not even possible. I've invited quick solution for rolling data.


Our data:


Screen Shot 2015-04-22 at 09.42.16.png


So we would like to calculate additional column in qlikView with rolling data...

Screen Shot 2015-04-22 at 09.42.37.png


...which will be working even when we select only one month:

Example solution for 3 months period with date in format YYYYMM


1. Create additional month table:


ROLLING_DATE_3M:
LOAD
YEAR_MONTH as YEAR_MONTH_3M_ROLLING,
Num#(Text(Date(AddMonths(Date#(YEAR_MONTH, 'YYYYMM'),+ 1 - IterNo()), 'YYYYMM'))) as YEAR_MONTH
Resident SALE
while IterNo() <= 3;


2. expression

SET ROLLING_SUM = AVG({1<YEAR_MONTH_3M_ROLLING=P(YEAR_MONTH)>} SALE);

and that's all. It's enough to add as diemnsion:

YEAR_MONTH_3M_ROLLING

and as expression:

=$(ROLLING_SUM)

Here is an example result:

Screen Shot 2015-04-22 at 09.41.46.png

Qvw file is attached

Labels (1)
Comments
Not applicable

It is a good point, but I think that the best solution is pre-calculate the rolling average in the script.

You can find below my solution:

for i = 1 to 3

  Join(RAW_DATA)

  LOAD

  ID,

  Date(Floor(Date + 7*$(i)), 'DD/MM/YYYY') as Date,

  Data as Data_PW$(i),

  Resident RAW_DATA;

NEXT

FINAL_DATA:

LOAD

  ID,

  Date,

  Data,

  RangeAvg(Data, Data_PW1, Data_PW2, Data_PW3) as RollingAverage,

Resident

  RAW_DATA

;

DROP Table RAW_DATA;

This is for a 4 week rolling average, but you can change the number of iterations in the the loop and the RangeAvg() function.

If you need a month rolling average you must use the AddMonths() function.

If you need an accumulate, you must change the RangeAvg() function by the RangeSum() function.

I hope it would be useful!!!!

Version history
Last update:
‎2015-04-22 04:45 AM
Updated by: