I have been asked to do totals based on a rolling 52 weeks window by employee hours/activity
For example, I would want to select a week, ie. April 11 2010 (field name: PPEND_DT) for a particular employee (EMP_ID) and see their total amount of sick (EARNING_CODE) hours (DAY_ACTIVITY) for the last 52 weeks. I would want to see the total for that particular week, then the total for the last 52 weeks on a pivot chart
Then I should be able to select April 18, 2010 for the same criteria and see the last 52 weeks total beside that particular weeks activity.
I think this should be something simple, but I just can't get it to work the way I want. I've tried rangesum, but it doesn't seem to be giving me to totals anywhere close to what I am expecting.
Alas, Dates are not QlikView's strength, so it actually isn't simple, even if it should be.
If you're forced to select one and only one week, you could use set analysis. Exact syntax will depend on how your weeks are defined, but hopefully you get the basic idea.
sum(Sales) // sales for selected week sum({<Week={">$(=date(Week-7*52)) <=$(=Week)"}> Sales) // Sales for past 52 weeks
If you want to be able to see multiple weeks at once, with rolling 52 weeks for each, I'd do it with data:
As of Week, Type, Week 20100822, This Week, 20100822 20100822, 52 Weeks, 20100822 20100822, 52 Weeks, 20100815 20100822, 52 Weeks, 20100808 ...
Then use As of Week and Type as chart dimensions, and sum(Sales) as the expression.
Lots of variations on the theme. Depends on what you specifically need.
We had a similar issue with some of our charts, most of our data is held by year & week and the problem was if I wanted to go back 8 weeks it was fine for week 9 & above simply minus 1 from the week number and display that, unfortunately if it wanted to span years this did not work, we tried doing this with dates and found the solution to be quite messy. The option we finally took was when we create our calendar table use a simple iteration of the day/week numbers, so now if we need to go back for any period of time we know the day/week number where we started and just take of the required number of days/weeks. This might/might not be feasible for yourselves but I thought I share our decision ...
