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.

Thanks,

Answers


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
    

see http://www.nrf.com/modules.php?name=Documents&op=viewlive&sp_id=6019

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][0]
    except:
        #print "fail5"
        return 5

    try:
        lenprevprevmth = seq[-3:-2][0]
    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:

RatailCalendar-4-5-4

DataNumber: date in number format

Year: Year

Quarter: Quarter (number)

Month: Month (number)

Week: week

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

Need Your Help

How can a reference fields on an Access form using a variable?

ms-access ms-access-2007 access-vba ms-access-2010

I have 20 text boxes on an Access 2010 form called [P101] to [P110] which refers to fields [P101] to [P110] in the source table. The may contain a value or not, but if not I do not want to see the...