Rolling weeks calculation

[#1]

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

[#2]

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.

[#3]

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

[#4]

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

[#5]

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.

[#6]

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.

RR

[#7]

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)

[#8]

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)

[#9]

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.

[#10]

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.