A company performing financial performance analytics year over year on consumer data may run into the challenge of gaining trust regarding the metrics from internal stakeholders. A common pain-point can be attributed to holidays (and their associated sale campaigns) not lining up each year. A reoccurring criticism we have seen at BlueGranite is summed up with the following real-life scenario: “I don’t trust these numbers because Easter was on a different week last year and our stores sell a lot of Easter chocolates.” In this post, we’ll walk through how to build a comparative system to aid in this instance.

The Problem

In Retail, financial performance is connected to sale campaigns, which are often associated with holidays or specific times of the year.

The Solution

To aid in analytics comparing year over year, a comparison system can be built. One way to implement a comparison system is to include comparison dates in a date dimension. Two key components can be implemented to make the comparison easier:

1. Include 4-5-4 calendar attributes. The National Retail Federation publishes a 4-5-4 calendar, which includes week numbers for each retail year. The retail year starts the Monday of the week that includes Feb 1, unless there are more than four days of January in that week. If there are more than four days of January in that week, then the retail year begins on the Monday following Feb 1.
2. Include holidays (or other recurring, date specific high-sales times).

The SQL code below is one way to solve this challenge and set up analytics to satisfy stakeholders that the numbers are accurate and relevant.

SQL Build-out

Building a successful date dimension for comparative date analytics involves:

1. Determining the retail year beginning.
2. Building out traditional date dimension attributes with 4-5-4 retail week attributes.
3. Including the holidays/seasonal events you wish to track.
4. Conjoin dates year over year to build out comparisons based on your business requirements.

Determine Retail Year Beginning

DECLARE @originalbegin DATE = @beginDate
SET @beginDate = DATEADD(day,-372,@beginDate)
DECLARE @EarliestPossibleDate DATE = '1753-01-01' 
DECLARE @ModifiedJulianDateStart DATE = '1858-11-17'
DECLARE @StartYearFeb1 DATE = CAST(CONCAT(STR(Year(@beginDate)),'-02-01') AS DATE)
DECLARE @RetailYearStart DATE = CASE WHEN MONTH(@beginDate) <> 1 THEN
                                CASE WHEN DATEPART(DW, @StartYearFeb1) <=4 THEN DATEADD(WEEK, DATEDIFF(WEEK, 0, @StartYearFeb1), 0)-1 ELSE DATEADD(WEEK, DATEDIFF(WEEK, 0, @StartYearFeb1), 0)+6 END
                                ELSE CASE WHEN DATEPART(DW, DATEADD(year,-1,@StartYearFeb1)) <=4 THEN DATEADD(WEEK, DATEDIFF(WEEK, 0, DATEADD(year,-1,@StartYearFeb1)), 0)-1 ELSE DATEADD(WEEK, DATEDIFF(WEEK, 0, DATEADD(year,-1,@StartYearFeb1)), 0)+6 END
                                END --- Retail year starts the Monday of the week that includes Feb 1, unless there are more than 4 days of January in that week.  If there are more than 4 days of January in that week, then the retail year begins on the Monday following the first of February

Build Out Traditional Date Dimension with 4-5-4 Retail Week Attributes

BEGIN
	WHILE @beginDate <= @endDate BEGIN SET @RETAIL_WEEK_NUMBER = DATEDIFF(week,@RetailYearStart,@beginDate)+1 IF @RETAIL_WEEK_NUMBER = 53 BEGIN IF DAY(@beginDate)>28
			BEGIN
				SET @RETAIL_WEEK_NUMBER = 1
			END
			IF DAY(@beginDate)<=28
			BEGIN
				SET @RETAIL_WEEK_NUMBER = 53
				--There were more than 4 days of Jan in week 53. Adding week 53
			END
		END
		IF @RETAIL_WEEK_NUMBER = 1 
		BEGIN
			SET @RETAIL_YEAR = @RETAIL_YEAR + 1
		END;

		WITH cte AS(
		SELECT @beginDate AS TheDate
			,@DateNumber as date_nbr
			,DATEADD(MONTH, [email protected],@beginDate) AS FiscalDate 
            ,DATEADD(YEAR, DATEDIFF(YEAR, 0, @beginDate), 0) AS YearStartDate
		UNION ALL
		SELECT DATEADD(DAY, 1, TheDate)
			,date_nbr +1
			,DATEADD(DAY, 1, FiscalDate) 
            ,DATEADD(YEAR,DATEDIFF(YEAR, 0,DATEADD(DAY, 1, TheDate)), 0)
		FROM cte
		WHERE date_nbr <7
		)
		INSERT INTO @DATES_TABLE
			SELECT TheDate, FiscalDate, YearStartDate
			FROM cte
			ORDER BY TheDate ASC

		INSERT INTO @RetailDatesTable
                SELECT TheDate, FiscalDate, YearStartDate, @RETAIL_WEEK_NUMBER, @RETAIL_YEAR FROM @DATES_TABLE;

		SET @beginDate = DATEADD(DAY, 7, DATEADD(WEEK, DATEDIFF(WEEK, 0, @beginDate), 0)-1)
		SET @RETAIL_WEEK_NUMBER = @RETAIL_WEEK_NUMBER + 1
		SET @StartYearFeb1 = CAST(CONCAT(STR(Year(@beginDate)),'-02-01') AS DATE)
		SET @RetailYearStart = CASE WHEN MONTH(@beginDate) <> 1 THEN
                                       CASE WHEN DATEPART(DW, @StartYearFeb1) <=4 THEN DATEADD(WEEK, DATEDIFF(WEEK, 0, @StartYearFeb1), 0)-1 ELSE DATEADD(WEEK, DATEDIFF(WEEK, 0, @StartYearFeb1), 0)+6 END
                                       ELSE CASE WHEN DATEPART(DW, DATEADD(year,-1,@StartYearFeb1)) <=4 THEN DATEADD(WEEK, DATEDIFF(WEEK, 0, DATEADD(year,-1,@StartYearFeb1)), 0)-1 ELSE DATEADD(WEEK, DATEDIFF(WEEK, 0, DATEADD(year,-1,@StartYearFeb1)), 0)+6 END
	   END
	   SET @DateNumber=1
		DELETE FROM @DATES_TABLE


	END
END

Create CTE to Track Desired Holidays
The below example only contains four, but this expanded code contains all the main US holidays, and any others applicable to your business can easily be added.

holidays ( holidayName, holidayYear, holidayDate ) AS (
-- New  Years
SELECT   'New Years' AS Holiday ,
YearNumber AS [Year] ,
TheDate AS [Date]
FROM     cte3
WHERE    DAY(TheDate) = 1 AND MONTH(TheDate) = 1

UNION

-- New  Years (Observed)
SELECT   'New Years (Observed)' AS Holiday ,
YearNumber AS [Year] ,
CASE WHEN DATEPART(DW, TheDate) = 1
THEN DATEADD(DAY, 1, TheDate)
WHEN DATEPART(DW, TheDate) = 7
THEN DATEADD(DAY, -1, TheDate)
ELSE TheDate
END AS [Date]
FROM     cte3
WHERE    DAY(TheDate) = 1 AND MONTH(TheDate) = 1 AND DATEPART(DW, TheDate) IN ( 1, 7 )

UNION

-- Martin Luther King Day: Third Monday in January
SELECT   'Martin Luther King Day' ,
YearNumber ,
MAX(TheDate)
FROM     cte3
WHERE    MONTH(TheDate) = 1 AND DATEPART(DW, TheDate) = 2 AND DAY(TheDate) < 22
GROUP BY YearNumber

UNION

-- Valentine's Day
SELECT   'Valentines Day' ,
YearNumber ,
TheDate
FROM     cte3
WHERE    MONTH(TheDate) = 2 AND DAY(TheDate) = 14

Continue based on what holidays you would like to include.

Incorporate Comparison Days by Joining Together Year over Year
In the attached SQL, I have used the following rules to determine comparison, but it can easily be modified to include logic appropriate to your business:

• Holidays have a comparison date of the same day that holiday fell on the previous year.
• The week leading up to and the day after Christmas and Easter match year over year.
• Black Friday matches year over year.
• The Saturday through Wednesday before Thanksgiving and 10 days after Thanksgiving aligns year over year.
• Columbus/Memorial/Presidents/Labor/MLK weekends match year over year.
• If there are no holiday comparison rules, then the comparison date is the same day of the week for the same retail week the previous retail year. For example, Monday of retail week 30 2020 has a comparison date of the Monday of retail week 30 in 2019.
  • For retail years that have 53 weeks, retail week 53 has comparison dates for retail week 1 of the same year. For the retail years which fall after a year with 53 weeks, week 1 is compared to week 2 of the previous year. This is called Year XXXX restated by the NRF.

HolidaysComp1 (holidayName,TYHolidayMatchDate,CompDate,DayCount) AS (
----Every Holiday on the above calendar will be matched to the holiday the previous year
SELECT  ha.holidayName, ha.holidayDate AS TYHolidayMatchDate, hb.holidayDate AS CompDate,0
FROM holidays ha
LEFT JOIN holidays hb ON hb.holidayName=ha.holidayName AND ha.holidayYear = hb.holidayYear +1

---- Holidays below match the day before.  You can add additional holidays (or remove them) to the IN() statement if there are sales initiatives associated with the day prior to another holiday

UNION

SELECT  ha.holidayName, DATEADD(day,-1,ha.holidayDate) AS TYHolidayMatchDate, DATEADD(day,-1,hb.holidayDate) AS CompDate,1
FROM holidays ha
JOIN holidays hb ON hb.holidayName=ha.holidayName AND ha.holidayYear = hb.holidayYear +1
WHERE ha.holidayName IN('Christmas','Columbus/Indigenous Peoples Day','Easter','Fathers Day','Halloween','Labor Day','Martin Luther King Day','Memorial Day','Mothers Day','Presidents Day','Fourth Of July','New Years','St Patricks Day','Thanksgiving','Valentines Day')

----Holiday Matches two days before.  With the exception of Christmas, Easter, and Halloween, this lines up on weekends. You can add additional holidays (or remove them) to the IN() statement if there are sales initiatives associated with the day prior to another holiday
						
UNION

SELECT  ha.holidayName, DATEADD(day,-2,ha.holidayDate) AS TYHolidayMatchDate, DATEADD(day,-2,hb.holidayDate) AS CompDate,2
FROM holidays ha
LEFT JOIN holidays hb ON hb.holidayName=ha.holidayName AND ha.holidayYear = hb.holidayYear +1
WHERE ha.holidayName IN('Christmas','Columbus/Indigenous Peoples Day','Easter','Fathers Day','Halloween','Labor Day','Martin Luther King Day','Memorial Day','Mothers Day','Presidents Day','Thanksgiving')

UNION
						
SELECT  ha.holidayName, DATEADD(day,-3,ha.holidayDate) AS TYHolidayMatchDate, DATEADD(day,-3,hb.holidayDate) AS CompDate,3
FROM holidays ha
LEFT JOIN holidays hb ON hb.holidayName=ha.holidayName AND ha.holidayYear = hb.holidayYear +1
WHERE ha.holidayName IN('Christmas','Easter','Thanksgiving')

Continue based on the rules above.

Comp Sales =
VAR CompDate =
    MAX ( 'Date'[CompDate] )
RETURN
    CALCULATE ( SUM ( data[Extended Price] ), 'Date'[Date] = CompDate )

Comp Quantity =
VAR CompDate =
    MAX ( 'Date'[CompDate] )
RETURN
    CALCULATE ( SUM ( data[Quantity] ), 'Date'[Date] = CompDate )