More on Same Day Last Year

Back in March, I published a post on calculating the same day last year.

In a recent post on the Masters Summit for QlikView LinkedIn group (the group is only open to previous attendees - yet another good reason to attend!), someone was asking about the subject so I shared a link to my original post. Another member queried the logic and suggested that it only works "75% of the time".

I expanded a bit on my logic from the original post, so I though that I would update it here for public viewing.

My assumption was that by 75% he was really just mentally rounding and actually meant 83%. In the last 100 years (04-Jan-1915 to 28-Dec-2014) there have been 17 years with 53 weeks. I assumed that this was the "25%" where he assumed that the logic broke down.

I believe that the logic doesn't break down - ever.

As an example, let us consider 2009 which had 53 weeks - 28-Dec-09 to 03-Jan-2010. What should be the correct week in the past to compare this week to? After all, there is no week 53 in 2008. Should we just forget that this week exists?

Of course not.  What we really need to do is to consider the purpose of the comparison and that is to compare performance of equivalent weeks and equivalent days in those weeks. The equivalent week, Monday to Sunday,  to make the comparison to would have to be the week of 29-Dec-08 to 04-Jan-09 - and that is actually week 1 of 2009!

This logic continues through 2010. 2010-01 is compared to 2009-02, 2010-02 is compared to 2009-03, etc., all the way until 2010-52 is compared to 2009-53. The week numbers will then realign in 2011.

In this fashion, the Date-364 calculation is always correct (leap year, 53 week year or otherwise) because it always gives you the equivalent day one year ago.

For your pleasure, here is a small script that you can run in QlikView or Qlik Sense to compare the like-for-like dates for the last 100 years:

Let vStart=Floor(MakeDate(1915,1,4));
Let vEnd=Floor(MakeDate(2014,12,28));
Let vDiff=vEnd-vStart+1;

Calendar:
Load
DateID,
Date(DateID) As Date,
WeekYear(DateID) as WeekYear,
Year(DateID) As Year,
Week(DateID) As Week,
WeekDay(DateID) As WeekDay,
WeekYear(DateID) & '-' & Num(Week(DateID), '00') As YearWeek,
Date(DateID-364) As Date_LFL,
WeekYear(DateID-364) & '-' & Num(Week(DateID-364), '00') As YearWeek_LFL
;
Load 
$(vStart)-1+RecNo() As DateID
AutoGenerate($(vDiff));

---

Stephen Redmond is author of Mastering QlikView, QlikView Server and Publisher and the QlikView for Developer's Cookbook
He is CTO of CapricornVentis a Qlik Elite Partner.