4 Replies Latest reply: Jan 10, 2013 11:39 AM by Ralph Graham

# Previous 6 Weeks

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

• ###### Re: Previous 6 Weeks

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:

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)

• ###### Re: Previous 6 Weeks

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

• ###### Re: Previous 6 Weeks

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.

• ###### Re: Previous 6 Weeks

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

Thanks,

Ralph