topic Rolling weeks calculation in QlikView

Rolling weeks calculation

Wed, 02 Mar 2011 14:49:20 GMT

Hello Experts,

I think its a basic query, but still tough as I am new to this. I have a chart which shows sales vs forecast for 52 weeks. However the requirement is this needs to be rolling 52 weeks, as in currently in week 10 for 2011, then the chart should show backwards till the week 11 for 2010, how can I achieve this?? Currently I have this

sum({<TIME_PERIOD_WEEK_NO>}INV_GROSS_REV)

Thanks in Advance

Rolling weeks calculation

Wed, 02 Mar 2011 15:08:26 GMT

I'm not quite sure what you can't achieve, but I'll give it a try:

- Limit the dimension (= TIME_PERIOD_WEEK_NO) from the current week till the current week - 52.

-Probably you will still see week 1 till 52 (where 1 till 10 is 2011 and 11 till 52 is 2010) in the chart. I solved this by renaming it to year-week (e.g. 2010 50), in that way you can still sort in the right way.

Rolling weeks calculation

Wed, 02 Mar 2011 15:13:33 GMT

Thank you for the quick reply, I have a Year week dimension as well, but how do I do the current - 52 weeks for rolling weeks calculation in the expression. Can you help me with this further please??

Thanks in Advance

AW:Rolling weeks calculation

Wed, 02 Mar 2011 15:33:57 GMT

Hello new_user3,

I think you want always start from current date(week) and then go back 52 weeks. My suggestion isn't totally correct because I am going back twelve month instead of 52 weeks, but at least it should bring you into the right direction.

sum({< TIME_PERIOD_WEEK_NO= { "$(= '<=' & week(addmonths( today(), -12)) & year(addmonths( today(), -12)) )" } >} INV_GROSS_REV)

E.g. replace your TIME_PERIOD_WEEK_NO with the according rolling week and update the syntax to it.

Regards, Roland

AW:Rolling weeks calculation

Wed, 02 Mar 2011 15:53:57 GMT

Hello Roland,

Thank you for the response, I have the same Time dimension ID for week and I used the below expression, nothing seems to be working though.

sum({< TIME_PERIOD_WEEK_NO= { "$(= '<=' & week(addmonths( today(), -12)) & year(addmonths( today())"}>}INV_GROSS_REV)

Could you please help me further with this? I am thinking if I am missing any variables or anything.

AW:Re: AW:Rolling weeks calculation

Thu, 03 Mar 2011 16:30:28 GMT

Hi new_user,

your expression seems to be ok. Be sure that the format of TIME_PERIOD_WEEK_NO and your search-string (the result of "$(=...)") match together. E.g. the existance of leading zeros or trailing blanks and so on may be missleading; "09-2010" is not the same as "9-2010" and also not equal to "092010".

Hint: If you don't use the label (leave it empty) QV will show you exactly the result of the according expression within the label. This can be usefull during testing. If you don't reach your goal, don't hesitate to send a little (anonymized) application to look into it.

Rolling weeks calculation

Thu, 03 Mar 2011 16:41:15 GMT

I two variables.

vmaxweek = latest week
vweeksago = how many weeks you want to go back (i.e. 52)

This expression works for me.

sum({<Week={">$(=$(vmaxweek)-$(vmaxweeksago)) <=$(vmaxweek)"}>} Sales)

Rolling weeks calculation

Thu, 03 Mar 2011 16:41:18 GMT

I created two variables.

vmaxweek = latest week
vweeksago = how many weeks you want to go back (i.e. 52)

This expression works for me.

sum({<Week={">$(=$(vmaxweek)-$(vmaxweeksago)) <=$(vmaxweek)"}>} Sales)

Rolling weeks calculation

Thu, 03 Mar 2011 17:59:14 GMT

Hello Roland,

Thank you for the response, but I was thinking of the solution provided by Henry, as that was what came to my mind first when I looked at the requirement.

Henry can you please tell me more about the vmaxweek variable as in I did a vmaxweek = week(today()) and is this correct and I also when I have a dimension as TIME_PERIOD_WEEK_NO, so should I create a variable specific to this dimension?? Can you explain this further please?

I think its around this, however if unable to get it then I will post a sample QVW to make you people understand it better.

Thanks for all your help.

Rolling weeks calculation

Thu, 03 Mar 2011 20:06:09 GMT

Sounds like your 'TIME_PERIOD_WEEK_NO' is a number series that corresponds with weeks. If it is indeed a number series (e.g. week 1 of 2010 = 1, week 2 of 2010 = 2....week 52 of 2010 =52, week 1 of 2011 = 53 etc.), then you can use the suggestion I provided.

To take an example, let's say you're starting from the 1st week of 2011 (week 53 according to the series mentioned above) and want to sum up the last 52 weeks.

In your load script just create the two variables:

SET vmaxweek = 53;

SET vminus = 52;

Then create the input box so that they can be changed/referenced in your dashboard.

The expression you should put into your table is:

sum({<TIME_PERIOD_WEEK_NO={">$(=$(vmaxweek)-$(vminus))<=$(vmaxweek)"}>}Sales)

hope this works.

Rolling weeks calculation

Mon, 11 Apr 2011 12:07:30 GMT

Hello,
Even this doesnt seem to have solved the problem I have. I will explain again I need to show a chart with current - 52 weeks of data at any time. which would be like starting from year 2010, weeks 15,16,17,18,19,20...... to Year 2011 Current Week 16. The problem I have is that the chart should show 2 expressions, one for SALES and other for FORECAST. The sales is only till current week but the forecast data is for full year. so in the chart its showing current year forecast full 53 weeks and sales only till week 16. How do i fix it to show both the data only till Current week - 52 weeks?

Any help would be appreciated. Will provide more info if needed.

ANDY