Qlik Community

QlikView Documents

Documents for QlikView related information.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.

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

hubertdudek
New Contributor III

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

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)
Attachments
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
Revision #:
1 of 1
Last update:
‎04-22-2015 04:45 AM
Updated by: