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...
Using the "edit" of looking at the function "WeekName()" I was able to get what I wanted, however the "split" between 1 Jan 2012 being week 52 of 2011 is still a thorn. This got me the closest so far in look & feel.
