topic Re: 20 Week rolling in QlikView

20 Week rolling

Wed, 25 Nov 2020 16:16:04 GMT

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,

Re: 20 Week rolling

MK_QSL — Tue, 26 Apr 2016 07:53:41 GMT

Calculating rolling n-period totals, averages or other aggregations

Re: 20 Week rolling

Tue, 26 Apr 2016 07:57:26 GMT

Hi Manish,

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

Kind regards

Re: 20 Week rolling

MK_QSL — Tue, 26 Apr 2016 08:18:24 GMT

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

Re: 20 Week rolling

Tue, 26 Apr 2016 08:20:02 GMT

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;

Re: 20 Week rolling

MK_QSL — Tue, 26 Apr 2016 08:32:28 GMT

Yes..!

Re: 20 Week rolling

Kushal_Chawda — Tue, 26 Apr 2016 08:43:58 GMT

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

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

Re: 20 Week rolling

Tue, 26 Apr 2016 08:52:44 GMT

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 .

Re: 20 Week rolling

MK_QSL — Tue, 26 Apr 2016 08:58:30 GMT

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

Re: 20 Week rolling

Tue, 26 Apr 2016 09:04:38 GMT

Thank you very much in advance Manis

Re: 20 Week rolling

MK_QSL — Tue, 26 Apr 2016 09:14:15 GMT

Based on the data you have provided,

you can simply create a Straight Table

Dimension

[1/20Week]

Expression

SUM(Hours)
SUM(Workers)

Re: 20 Week rolling

Tue, 26 Apr 2016 09:16:15 GMT

Hi Manish,

I was wondering if its possible to let Qlikview develop this. Now I manually produced this by marking the year-weeks which needed to be marked as data for 2016-1 or 2016-2.

2nd if I would create a table marking which weeks would be collected per rolling 20 weeks how should I use this data in a pivot table to give me the much needed 20 weeks rolling?

Re: 20 Week rolling

MK_QSL — Tue, 26 Apr 2016 09:56:45 GMT

What QV can handle and what can't ... would be answered only if you provide proper raw data...

Re: 20 Week rolling

Tue, 26 Apr 2016 10:33:47 GMT

HI,

Just let me know which calendar fields u have like, Year , Week , Year-Week

It looks like you need to generate [1/20Week] field data which will further comes along with [20Week] respect to Hour & Worker data to present report like below,

	Hours	Workers
2016-1	524	220.7143
2016-2	762.8571	592.1429
2016-3	1105.714	963.5714
2016-4	1448.571	1335

Is this what you're looking for ?

Regards,

Re: 20 Week rolling

Thu, 12 May 2016 08:18:10 GMT

Hi,

Yes it needs to sum but also over the past year. So 2016-1 would calculate 19 weeks from 2015 & 1 week from 2016.

Kind regards.