Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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)
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)
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)
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
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.
I had to use a different approach although I'll use the above approach in future projects.
Thanks,
Ralph