TY/LY Relation

Every TY (this year's) day, week, period or quarter, has a corresponding LY (last year) day, week, period or quarter. This implicit mapping is straightforward for 52-week years. The acid test, however, is in handling 53-week years.

There are two common approaches for defining the TY-LY relationship:

1. The TY and LY have the same day number (1-371) and week number (1-53.) Higher levels, i.e. periods and quarters, are made up of the same days and weeks following a 4-4-5 pattern, so any TY day belongs to the same period as its corresponding LY day. The only oddity is that we have to skip the 53d week in year-to-year comparisons.
2. The alternative approach is to establish LY as 52 weeks back from any date or week of TY. This way we don't care about the absolute week number, and always take a rolling 52-week view of the year. Same periods and quarters however, don't necessarily contain the same weeks.

The first approach has the advantage that each day and week, corresponds to the same period and quarter of the year for both TY and LY. The potentially down-side of this approach is that the 53d week, whenever it occurs, is ignored for TY/LY comparisons. If the year end is defined at the end of December, this last week typically contains the holiday season that no retailer is willing to disregard. The second approach solves this problem, as it always compares days and weeks in a way that maps fixed holidays, such as Christmas/New Year fairly close. The penalty is that these weeks are not fitting neatly in respective periods and quarters. Actually, TY-week-53 corresponds to LY-week-01 of the same calendar year. This violates the symmetry that we expect from any dimension, because LY weeks (and days) don't fit inside the respective LY periods.

To illustrate the asymmetry in the second approach, consider a calendar where year 2008 includes a 53d week. Let's assume we are in 2009W05 and are interested in comparing the first fiscal period of 2009 (2009P1) with that of the corresponding LY period. But what weeks make up LY of 2009P01? Is it 2008P01? or is it a fictitious period made-up of the four LY weeks of the weeks that make-up 2009P01?

What if the user after getting the results of his query at the period level, drills down to get the same information at weekly grain. If the first (period-level) query is answered by querying 2009P01 and 2008P01, then the week drill down will be off by a week, since the weeks making-up period 2009P01 are 2009W01, 2009W02, 2009W03 and 2009W04, with corresponding LY weeks: 2008W02, 2008W03 and 2008W04 and 2008W05 - computed 52 weeks back from the TY weeks. Since we cannot have the sum of the weeks not make-up the period amount, we should probably consider that the LY of 2009P01 should be the sum of the respective LY weeks.

This is a reasonable approach, as long as we remember that to get period level information we cannot use the natural parent of 2008W02 - 2008W05, as we would end-up with three weeks belonging to P01 and one in P02. So the right method would be to query the fact using the sum of the weeks or days by moving back 52 weeks, and label the period using the labels of TY. This way the four weeks from 2008W02 to 2008W05 would be properly labeled as P01 when viewed as LY P01.