I have a bunch of records per day, which I average totals on specific service types within the company.
There's a column that gives me the month / year combo the data is in as well, so I have a "total for month" average pivot table, which if you click the month, it will show the daily pivot table for that month.
I now want to bring in "Per Week". The problem I have now is that Week 52 last year stretched to the first day of the new year. (1st of January 2012 was one of the days within the week 52 of 2011)
This makes it LOOK like 2012 already has a week "52" since I show the data per week of the year when in actual fact it's 1st of January 2012 with "week number" 52 of the previous year.
This is obviously confusion and scues the averages for Week 52 of 2011 a bit since it "throws away" the 1st of Jan 2012 which is still a part of it.
Does anyone have an idea of how I can handle this? if I don't include the year somehow, all the data at once confuses the user (as it can stretch over multiple years and give the wrong averages). I also noticed that if you click on a month like December 2011, it will only shows the data for december even though the "weeks" number stretches till 1st of Jan 2012 in week 52...
What about making use of weekyear() function? Create a new field from your date field and use this to filter your data (in combination with week() function). So you only get one week 52 per (week-) year.
weekyear(date)
The year to which the week number belongs. The week number ranges between 1 and approximately 52. Some years week #1 starts in December, e.g. December 1997. Other years start with week #53 of previous year, e.g. January 1999. For those few days when the week number belongs to another year, the functions year and weekyear will return different values
edit:
You may also want to look into the other date and time functions, for example weekname() might be of interest, too.
What about making use of weekyear() function? Create a new field from your date field and use this to filter your data (in combination with week() function). So you only get one week 52 per (week-) year.
weekyear(date)
The year to which the week number belongs. The week number ranges between 1 and approximately 52. Some years week #1 starts in December, e.g. December 1997. Other years start with week #53 of previous year, e.g. January 1999. For those few days when the week number belongs to another year, the functions year and weekyear will return different values
edit:
You may also want to look into the other date and time functions, for example weekname() might be of interest, too.
the question is, where do you want to put all dates of the year, every first days of the year in week 1 and every last days of the year in the last week 52, or 53? Then use this function in the script:
If(Month(Date)=1 And Week(Date)>50, 1, If(Month(Date)=12 And Week(Date)< 5, Week(Date-3),Week(Date)))
Thanks, this effectively just moves the 1st of Jan 2012 (part of week 52) to week 1 of 2012.
It *could* work, but would it be acceptable to business at the end of the day? Because 1st of Jan 2012 is technically still part of the averages for week 52 in 2011...
I'll play around with it and see. Currently, for some reason, as soon as I try to sort the data, the selection gets "stuck" on that particular data view and I have to Ctrl+Shift+D just to get back.
How would you go about sorting this without that happening? ie:
That's exactly what I did, in my script I have (abbreviated)
SELECT Week(DailyDateTimeStamp) as myWeek, DailyDateTimeStamp, (other data) FROM Main.qvd
I then used your if statement:
If(Month(DailyDateTimeStamp)=1 And Week(DailyDateTimeStamp)>50, 1, If(Month(DailyDateTimeStamp)=12 And Week(DailyDateTimeStamp)< 5, Week(DailyDateTimeStamp-3),Week(DailyDateTimeStamp)))
Which pushed the 1st of Jan 2012 into Week 1 instead of keeping it in Week 52
I then used both your examples to sort by using "myWeek" (the new field I created in script), but neither worked as expected.
Thanks for the example but it doesn't even come close into what I want, here is sort of a sample with the data I'm working with:
datetimestamp,location,time-in,time-out
I then have a chart that shows me the average time taken per location for that month and another chart that shows me the average time taken per location for that day. These two are pivot tables so that the locations appear at the top from left to right next to the total average for that month or day (depending on which chart you're looking at)
The month chart would obviously have rows per month (sorted by month/year, so it goes Dec 2011, Jan 2012.... you know, in the RIGHT order). The day chart will have it per day, sorted from the 1st of the month to the last day of the month. If no selection on the Month chart appears, it will show the day chart sorted by day/month/year. So 1-31 Dec 2011, 1-31 Jan 2012 etc.... the RIGHT order in date.
I'd like to now add a Weekly summary (much like the daily and monthly ones)
I have it now that it displays the week summary when clicked on a month. I can then click on a week in that month to display the day's within that week and show their summary. But when month isn't selected, I'd like to (just like I do with the day chart) display all weeks, but in the RIGHT order, ie: 49,50,51,52 (for year 2011) 1, 2, 3, 4, 5, 6, 7, 8 etc (for 2012 and beyond)
The label would obiously display something like 49 - 2011
So far Martina's suggestions has got me to the point where it works like I want it to, however it doesn't SORT the way I want it. I've tried both her suggestions and have gone into yours as well with no luck.
I did go into your initial suggestion and it did give me more insight into the issue, Martina's solution provided an "easy" way to group 1st of Jan 2012 (week 52 of 2011) with week 1 of 2012, it's just a question of sorting it, and nothing I've tried has given me: 49,50,51,52,1,2,3,4,5,6,7
You'd think if data (in an expression) looked like this:
201149
201150
201151
201152
201201
201202
201203
201204
That the QV engine would sort it Ascending like that. But it doesn't, even though 201 > 152
