January 6, 2016 at 4:01 pm
I built a report 6 months ago that graphs sales figure for the previous three weeks from the run date against each of the last 3 years for the same weeks. I was using isowk in the DATEPART function to allow me to day adjust the three graphs so each graphs days would line up for the three week period.
As we have peakorders on fridays this shows up better if orders were up or down each friday compared to the other years.
my problem now is as the three week period is now spanning over two years 2015/2016 i failed to anticipate that some years have 52 isoweeks(2014,2016) and some have 53 isoweeks(2015). I now have an isoweek 53 that i cant compare it with in 2014. getting a bit confused 🙁
thanks
January 7, 2016 at 5:08 am
Since we cannot change the laws of physics and are unlikely to get the world to accept an alternate calendar/clock system that does not align with daylight and seasons, the oddities of the calendar are a fact of life that everyone has to deal with.
The number of days in a year is not a multiple of 7. Every week number system has a problem with this - either you always have week numbers 1 - 53 but shorter weeks at start and end (American week numbers), or you do or do not have week number 53 (ISO). Both have issues.
In your case, the best solution I can think of is to accept that there is no data for week 53 of some years, try to come up with the best (read: least bad) way to visualize that in your report, and educate the managers working with the report. Second best solution would be to slightly rephrase the definition of the report to compare each week with the corresponding period 52 weeks earlier. The third best solution would be to reuse the week 52 numbers for week 53 in years that do not have a week 53, and the fourth best is to simply not show week 53 in your reports - but I really do not like the last two ideas, just throwing them out for completeness.
January 12, 2016 at 7:43 am
Thanks for your reply and sorry for taking so long getting back. My solution just now is to make week53 2015 to week1 2016. The data seem to fit but im now not sure im comparing like for like.
January 12, 2016 at 3:33 pm
ps_vbdev (1/12/2016)
Thanks for your reply and sorry for taking so long getting back. My solution just now is to make week53 2015 to week1 2016. The data seem to fit but im now not sure im comparing like for like.
You are not comparing like for like, but neither are you for any other week.
E.g week 51 of 2016 is the full week before Christmas - Monday Dec 19 to Sunday Dec 25. For many companies, sales in that week will be quite different from week 21 of 2015 (Dec 14 to Dec 20 - a lot of Christmas sales but not the last-day rush). Also, some holidays (Easter, Pentecost, etc) are not always in the same week, affecting the number of business days in those weeks.
A month-by-month comparison in most cases makes more sense. But if your management wants week-by-week comparison, then those differences are unavoidable, and they should already be aware of that.
January 13, 2016 at 2:27 am
Thanks, yes is a bigger issue ill need to have a think about. I have been wanting to move over to a 445 calendar but this still wont solve my 52-52 fiscal year issue.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply