20 Week rolling

[#1]

Hi all,

I am buildling a 20 week average that moves over the years. To make this happen I made an excel file containing 2 rows:

20 week average     20 Weeks

2016-2                    2016-2

2016-2                    2016-1

2016-2                   2015-53

2016-2                    2015-52

Etc etc etc

This would be week 2016-2 is 20 weeks back, week 2016-1 and 20 weeks back etc etc.

However I got 2 problems:

- 1 I was wondering if its possible to let Qlikview calculate these rows instead of building this in excel for all my weeks.

- 2 How could I connect/add this? I already got my QV app but I need to add/intergrate this function to the existing data structure.

Kind regards,

[MK_QSL]

Calculating rolling n-period totals, averages or other aggregations

[#3]

Hi Manish,

Does this work with weeks? Because most of the times the use of months is essential.

Kind regards

[MK_QSL]

It should work by using some tricks.. You can create WeekYear field and create WeekYearID using AutoNumber function.... This will make your job easy..

[#5]

Should I first create weekyear and weekyearID before applying it as below:

AsOfWeek:

load 'Current' as WeekType,

Week as Week_AsOf,

Week as Week

Resident Production;

Concatenate (AsOfWeek)

load 'Rolling 20' as WeekType,

Week as Week_AsOf,

Week + 1 - IterNo() as Week

Resident Production

while IterNo() <= 20;

right join load Week Resident Production;

[MK_QSL]

Yes..!

[Kushal_Chawda]

or if you have date field in your model try the below expression

=sum({<Date ={">=$(=max(Date)-120)<=$(=max(Date))"}>}Amount)

[#8]

Manish,

How would this work with 2016-1 -> 2015-53... 2015-49. Would it be able to count backwards instead of just starting with calculating at 2016-1... 2016-20 resulting in good data when I passed 2016-20 .

[MK_QSL]

Provide sample data in excel along with the result you are looking for. I will try to create script for you.

[#10]

Thank you very much in advance Manis