Previous 6 Weeks

[#1]

Hello,

I have encountered an issue at year end in relation to a variable and set analysis that I currently have. I have a set analysis that uses a variable which looks at data for the current week as well as the last 6 weeks worth of data, the original requirement was to look at a year at a time so the below expression worked fine as it simply looked at e.g. week 46, week 46 -1, week 46 -2, etc. However a new requirement is to look at data across multiple years which proves problematic at the start of a new year as the current variable will not take into account the previous years last few weeks. The only date information I have is week and year (not a specific date) so I was thinking that a numeric value should be attached to the week-year so that a week-year, week-year -1, week-year -2 would work. Any ideas?

Thanks,

Ralph

avg

({<KPI={"Total stock"},Week={"YTD",$(=vCurrentWeek),$(=(vCurrentWeek-2)),$(=(vCurrentWeek-3)),$(=(vCurrentWeek-4)),$(=(vCurrentWeek-5)),$(=(vCurrentWeek-1))}>} Data) 

[Gysbert_Wassenaar · Accepted solution]

Create a serial weeknumber in the script using the rowno() function. You can use the serial weeknumber instead of the original weeknumber. I don't know your data model, so I can't tell if you can use the rowno() directly on your source table or if you need to create a separate week table first with something like

Weeks:

load distinct

rowno() as serialweek,

week & '-' & year as week-year,

from ...sourcetable... ;

Create the week-year field in your source table too to associate the two tables

Your set analysis expression would become something like this I think:

avg

({<KPI={"Total stock"},Week={"YTD"}> +

  <KPI={"Total stock"},Year=,Week=,serialweek={">=$(=max(serialweek)-5)<=$(=max(serialweek))"}>}

Data) 

[#3]

Thanks, that is useful although I don't think it will work in this case. The datasource is a crosstable from Excel and is over 2 sheets so each actual week might have 100's of rows worth of data and the rowno() will obviously replicate this

[Gysbert_Wassenaar]

That's why I mentioned you might have to create a separate weeks table. The load distinct will create only one record per week, so rowno() can create a serialweek field.

[#5]

I had to use a different approach although I'll use the above approach in future projects.

Thanks,

Ralph