4-5-4 National Retail foundation Calendar csv download or function to create
I've been googling all over the place and haven't found this. The retail client I'm working for using the NRFretail calendar. NRF site Calendars I'm wondering if anyone has ever created a lookup/dimension table with these values.
You can find a perl module that can generate a Retail 4-5-4 calendar for any year on CPAN: http://metacpan.org/pod/DateTime::Fiscal::Retail454
It was written specifically for this problem.
An algorithmic option I've used in the past was (I'm paraphrasing as I did it in Excel):
- From the date, figure out the weeknum (in the range 1 to 53)
From the weeknum, calculate the period number (in the range 1 to 12) as:
1+ 'one-based 3*(INT((weeknum-1)/13))+ 'start from the first week within a 13-week quarter IF(MOD(weeknum-1,13)>3,1,0)+ 'adjust for weeks in the middle chunk IF(MOD(weeknum-1,13)>8,1,0)+ 'and for weeks in the last chunk IF(weeknum>52,-1,0) 'and handle "leap week" every 6th year or so
4-5-4 or 4-4-5 calendars are used to give consistent month lengths from year to year, so rather than reporting monthly sales from 1-31 January the month of January starts on say a sunday and ends Saturday. This way there is always a consistent number of weekends in a month and holidays don't move around, making monthly sales comparisoons from year to year feasible
there is a lot of fiddling behind the scenes but 4-5-4 calendars are published - see link above.
it sounds like you are trying to use them as a dimension for a star schema data warehouse? Are you looking for advice on implementing that?
I really should have done some real work this evening
******************* Feb06 [[29, 30, 31, 1, 2, 3, 4], [5, 6, 7, 8, 9, 10, 11], [12, 13, 14, 15, 16, 17, 18], [19, 20, 21, 22, 23, 24, 25]] ******************* Mar06 [[26, 27, 28, 1, 2, 3, 4], [5, 6, 7, 8, 9, 10, 11], [12, 13, 14, 15, 16, 17, 18], [19, 20, 21, 22, 23, 24, 25], [26, 27, 28, 29, 30, 31, 1]] ******************* Apr06 [[2, 3, 4, 5, 6, 7, 8], [9, 10, 11, 12, 13, 14, 15], [16, 17, 18, 19, 20, 21, 22], [23, 24, 25, 26, 27, 28, 29]]
the above seems correct eyeballing it against NRF calendars
its from the below monstrosity, but it might help you do 15 years
import calendar calendar.setfirstweekday(calendar.SUNDAY) import datetime import pprint def addweek(yr): d = datetime.date(yr, 12, 31) if d.weekday() in (6,0,1): return True else: return False def printday(d): print d.day, if d.weekday() == 5: print def wksinthismth(cal): seq = [len(mth) for mth in cal] #print "seq", seq #special - but really need dates in cal if (len(seq) % 11 == 0 ) and len(seq) != 0 and addweek(2006): return 5 try: lenlastmth = seq[-1] except: #print "fail4" return 4 try: lenprevmth = seq[-2:-1] except: #print "fail5" return 5 try: lenprevprevmth = seq[-3:-2] except: lenprevprevmth = 4 if lenprevmth == :return 5 if (lenlastmth == 4 and lenprevmth == 5 and lenprevprevmth == 5): #long january just passed return 5 #print lenlastmth, lenprevmth if (lenlastmth == 4 and lenprevmth == 4) : #print "ok5" return 5 else: #print "ok4" return 4 oneday = datetime.timedelta(days=1) startdate = datetime.date(2006,1,29) thisdate = startdate cal =  thismth =  thiswk =  for i in range(800): thiswk.append(thisdate.day) if thisdate.weekday() == 5: #week over, decide how format it wks = wksinthismth(cal) if len(thismth) <= wks-1: thismth.append(thiswk) else: cal.append(thismth) thismth =  thismth.append(thiswk) thiswk =  thisdate += oneday mthslist = ['Feb06', 'Mar06', 'Apr06', 'May06', 'Jun06', 'Jul06', 'Aug06', 'Sept06', 'Oct06', 'Nov06', 'Dec06', 'Jan07', 'Feb07', 'Mar07', 'Apr07', 'May07', 'Jun07', 'Jul07', 'Aug07', 'Sept07', 'Oct07', 'Nov07', 'Dec07', 'Jan08', 'Feb08', 'Mar08', 'Apr08', 'May08', 'Jun08', 'Jul08', 'Aug08', 'Sept08', 'Oct08', 'Nov08', 'Dec08', ] i = 0 for mth in cal: print "*******************", mthslist[i] pprint.pprint(mth) i += 1 # start week on Sun # if 31 dec is S M T then add week in Jan print [len(mth) for mth in cal]
Attacched a csv with a calendar from 2005 to 2025,with the followings:
DataNumber: date in number format
Quarter: Quarter (number)
Month: Month (number)
DataDate: date in string format
Day: day number
DayName:Day description (In italian)
CommercialWeek: commercial Week caluculated with DateTime::Fiscal::Retail454
CommercialMonth: commercial Month based on nrf.com 4-5-4 retail calendar
CommercialYear: commercial Year based on nrf.com 4-5-4 retail calendar
StartWeek: start commercial week aluculated with DateTime::Fiscal::Retail454
EndWeek: end commercial week aluculated with DateTime::Fiscal::Retail454
This is the perl script used to generate the calendar:
use DateTimeX::Fiscal::Fiscal5253; use Data::Dumper; use Data::Dumper::Table; use DBI; use strict; use JSON; use warnings; my $fc = DateTimeX::Fiscal::Fiscal5253->new( end_month => 1, end_dow => 7, end_type => 'closest', leap_period => 'last', year => 2019, ); my $filename = 'report.txt'; open(my $fh, '>', $filename) or die "Could not open file '$filename'$!"; print $fh Dumper(\$fc); close $fh;
The main difference with nrf.com 4-5-4 retail calendar is that the week is formatted in "Italian Style" starting on Monday.
Hope this help.
The script below is to create a table for 4/5/4 Retail Calendar for any time frame. It calculates any year containing 52 or 53 weeks. The week is formated in US style starting on Sunday.
I have created Calendar table before running the following script with the following structure and populated data into "dbo.Calendar". You might want to use the article by Aaron Bertrand https://www.mssqltips.com/sqlservertip/4054/creating-a-date-dimension-or-calendar-table-in-sql-server/ to populate data into the table "Calendar":
CREATE TABLE dbo.Calendar ( DateKey INT NOT NULL PRIMARY KEY, [Date] DATE NOT NULL, [Day] TINYINT NOT NULL, DaySuffix CHAR(2) NOT NULL, [Weekday] TINYINT NOT NULL, WeekDayShortName VARCHAR(3) NOT NULL, WeekDayLongName VARCHAR(10) NOT NULL, IsWeekend BIT NOT NULL, IsHolidayUS BIT NOT NULL, HolidayUSText VARCHAR(64) SPARSE, IsHolidayCA BIT NOT NULL, HolidayCAText VARCHAR(64) SPARSE, DOWInMonth TINYINT NOT NULL, [DayOfYear] SMALLINT NOT NULL, WeekOfMonth TINYINT NOT NULL, WeekOfYear TINYINT NOT NULL, ISOWeekOfYear TINYINT NOT NULL, [Month] TINYINT NOT NULL, [MonthShortName] VARCHAR(3) NOT NULL, [MonthLongName] VARCHAR(10) NOT NULL, [Quarter] TINYINT NOT NULL, QuarterName VARCHAR(6) NOT NULL, [Year] INT NOT NULL, YYYYMM CHAR(6) NOT NULL, MonthYear CHAR(7) NOT NULL, FirstDayOfMonth DATE NOT NULL, LastDayOfMonth DATE NOT NULL, FirstDayOfQuarter DATE NOT NULL, LastDayOfQuarter DATE NOT NULL, FirstDayOfYear DATE NOT NULL, LastDayOfYear DATE NOT NULL, FirstDayOfNextMonth DATE NOT NULL, FirstDayOfNextYear DATE NOT NULL, PeriodID INT NULL ); GO
The script creates the table "CalendarPeriods45" and populates data. The table "CalendarPeriods454" is linked to the table "Calendar" by "PeriodID". The script was created in Sql Server 2016, so you might need to change "DROP TABLE IF EXISTS..." to the "IF OBJECT_ID('dbo.CalendarPeriods454') IS NOT NULL DROP TABLE...".
DROP TABLE IF EXISTS dbo.CalendarPeriods454 CREATE TABLE dbo.CalendarPeriods454 ( PeriodID INT NOT NULL PRIMARY KEY, StartDate Date NOT NULL, EndDate Date NOT NULL, [Days] TINYINT NOT NULL, PeriodOfYear TINYINT NOT NULL, PeriodShortName VARCHAR(3) NOT NULL, PeriodLongName VARCHAR(10) NOT NULL, [Year] INT NOT NULL, [Quarter] TINYINT NOT NULL, QuarterName VARCHAR(6) NOT NULL, SalesReleaseDate Date NOT NULL, Season VARCHAR(6) NOT NULL ); GO DECLARE @StartDate datetime; -- date to load DECLARE @EndDate datetime; -- date to load SET @StartDate = '2000-01-30' -- should be beginning of the 454 Calendar year SET @EndDate = '2041-01-30' DECLARE @PeriodID INT; SELECT @PeriodID = ISNULL(s.periodID,0) FROM (SELECT MAX(periodID) as periodID from CalendarPeriods454) as s SET @PeriodID = @PeriodID + 1 DECLARE @PeriodOfYear TINYINT; --1 to 12 SET @PeriodOfYear = 1; DECLARE @Days TINYINT ; --28 or 35 DECLARE @PeriodShortName VARCHAR(3); DECLARE @PeriodLongName VARCHAR(10); DECLARE @Year int; SET @Year = Datepart(year,@StartDate); DECLARE @Quarter TINYINT; --1 or 2 or 3 or 4 While @StartDate<=@EndDate BEGIN SET @Days = (CASE @PeriodOfYear WHEN 1 THEN 28 WHEN 2 THEN 35 WHEN 3 THEN 28 WHEN 4 THEN 28 WHEN 5 THEN 35 WHEN 6 THEN 28 WHEN 7 THEN 28 WHEN 8 THEN 35 WHEN 9 THEN 28 WHEN 10 THEN 28 WHEN 11 THEN 35 WHEN 12 THEN 28 END) -- Exclusion if need to add additional week at the end of the year, check the last day of the period. -- If last day of the period equal 26 or 27 january we add additional week IF @PeriodOfYear = 12 and (datepart(day,dateadd(day,@Days-1,@StartDate))=26 or datepart(day,dateadd(day,@Days-1,@StartDate))=27) SET @Days = 35 SET @PeriodShortName = (CASE @PeriodOfYear WHEN 1 THEN 'Feb' WHEN 2 THEN 'Mar' WHEN 3 THEN 'Apr' WHEN 4 THEN 'May' WHEN 5 THEN 'Jun' WHEN 6 THEN 'Jul' WHEN 7 THEN 'Aug' WHEN 8 THEN 'Sep' WHEN 9 THEN 'Oct' WHEN 10 THEN 'Nov' WHEN 11 THEN 'Dec' WHEN 12 THEN 'Jan' END) SET @PeriodLongName = (CASE @PeriodOfYear WHEN 1 THEN 'February' WHEN 2 THEN 'March' WHEN 3 THEN 'April' WHEN 4 THEN 'May' WHEN 5 THEN 'June' WHEN 6 THEN 'July' WHEN 7 THEN 'August' WHEN 8 THEN 'September' WHEN 9 THEN 'October' WHEN 10 THEN 'November' WHEN 11 THEN 'December' WHEN 12 THEN 'January' END) SET @Year = (CASE @PeriodOfYear WHEN 1 THEN Datepart(year,@StartDate) ELSE @Year END) SET @Quarter = (CASE @PeriodOfYear WHEN 1 THEN 1 WHEN 2 THEN 1 WHEN 3 THEN 1 WHEN 4 THEN 2 WHEN 5 THEN 2 WHEN 6 THEN 2 WHEN 7 THEN 3 WHEN 8 THEN 3 WHEN 9 THEN 3 WHEN 10 THEN 4 WHEN 11 THEN 4 WHEN 12 THEN 4 END) INSERT INTO dbo.CalendarPeriods454(PeriodID,StartDate,EndDate,[Days],PeriodOfYear, PeriodShortName,PeriodLongName,[Year],[Quarter],QuarterName,SalesReleaseDate,Season) --Calendar 454-454-454-454 SELECT PeriodID = @PeriodID, StartDate = @StartDate, EndDate = dateadd(day,@Days-1,@StartDate), [Days] = @Days, PeriodOfYear = @PeriodOfYear, PeriodShortName = @PeriodShortName, PeriodLongName = @PeriodLongName, [Year] = @Year, [Quarter] = @Quarter, QuarterName =CONVERT(VARCHAR(6), CASE @Quarter WHEN 1 THEN 'First' WHEN 2 THEN 'Second' WHEN 3 THEN 'Third' WHEN 4 THEN 'Fourth' END), SalesReleaseDate = dateadd(day,4,@StartDate), Season = CASE WHEN @PeriodOfYear>=1 and @PeriodOfYear<=6 THEN 'SPRING' ELSE 'FALL' END SET @StartDate = dateadd(day,@Days,@StartDate) SET @PeriodID = @PeriodID+1 SET @PeriodOfYear = (CASE WHEN @PeriodOfYear+1 =13 THEN 1 ELSE @PeriodOfYear+1 END) END -- Table Calendar was created before at a daily grain -- we need to update Calendar Table to set PeriodID to link Calendar and alter table [dbo].DateDimension add PeriodID INT alter table [dbo].DateDimension add FW varchar(2) ;with cte as ( select DateKey , ((ROW_NUMBER() over(Order by DateKey asc)-1) / 7) + 1 as 'FW' , Cal454.PeriodID 'FM' FROM [dbo].DateDimension as C1 --FROM [dbo].Calendar as C1 INNER JOIN [dbo].[CalendarPeriods454] as Cal454 ON Cal454.StartDate<=C1.Date AND Cal454.EndDate>=C1.Date ) UPDATE c1 SET c1.PeriodID = cte.FM ,c1.FW = cte.FW FROM [dbo].DateDimension as C1 join cte on c1.DateKey = cte.DateKey