Recipe for a 4-4-5 Calendar

[hic]

Calendars are used in most Qlik apps. In most cases, a standard Gregorian calendar is used, but in some cases a more complex calendar is needed. This post is about how to create a week-based fiscal calendar of a 4-4-5 type.

[hic]

The 4-4-5 calendar is a week-based calendar, where the year is divided into 4 quarters, each with 3 months. The first month has 4 weeks, the second has 4 weeks, and the last has 5 weeks. Occasionally the 12th month has an additional week.

However, these quarters and months have nothing in common with the standard months. First, they are not in sync with the Gregorian calendar. The 4-4-5 year can for example start in the last week of August. Further, the 4-4-5 months do not have the same lengths as the Gregorian months.

One major advantage over a regular calendar is that the end date of the period is always the same day of the week, which is useful in planning. Similarly, the beginning of the year, the quarters and the months are all on the same day of the week. This means that different years start on different dates. In the table below this is clearly visible. The end of the year is blue and the beginning of the new year is green.

The definition of when the year starts is often described in a phrase like “the year ends on the last Saturday of August”. But this is the same as saying that

• All weeks start on Sundays.
• September 1st always belongs to week 1. This is the Reference Date.

These two bullets can be expressed as integer parameters, which can be used when generating the calendar in the Qlik script. For the first day of the week, 0 is used to denote Monday and 6 is used to denote Sunday. Further, the number of days between the reference date and the Dec 31st is used to define the beginning of the year.

Finding the reference day may sometimes take some thinking. To help you, I have compiled some examples:

Once the two parameters have been defined, a calendar can be created using e.g. the script that is found on Calendars. This script also creates some other calendars, e.g. 4-5-4, 5-4-4 and Broadcast calendars.

The logic in the script has many steps, but is still fairly straightforward. For each date, the script needs to

1. Find the week start of the date
2. Use the week start to find the reference date of the input date. Note that the relevant reference date sometimes is after the date itself.
3. Use the reference date to find which fiscal year the date belongs to. The start date of the year is also calculated.
4. Use the start of the year to calculate the day number within the year
5. Finally, the day number of the year can be used to calculate the remaining fields.

Summary: It is possible to create a script that generates a correct 4-4-5 calendar.  Don’t hesitate to download the script example and modify it, if you need a 4-4-5 calendar.

HIC

Further reading related to this topic:

Calendars

Redefining the Week Start

Redefining the Week Numbers

Redefining the Week Start in Qlik Sense

Non-Gregorian calendars

[pgrenier]

Very interesting article Henric!

There is also another model that is used which consists of two notions: the 4-4-5 fiscal months within fixed, Gregorian-based fiscal quarters. The last week of each quarter is therefore an partial week, but this does offer a simplified approach for issuance of quarterly statements.

Cheers!

Philippe

[hic]

Interesting. In this calendar, do weeks of 2016 Q1 all start on Fridays (since Jan 1 is a Friday) while weeks of 2016 Q4 start on Saturdays (since Oct 1 is a Saturday)?

HIC

[pgrenier]

Apparently, in what I can see in the system's data, weeks cross over fiscal period or quarters.

It's quite a dilemma, as period 2 and 3 of a given quarter always start on a Sunday, while period 1 starts the day after the prior quarter's last day. And, the last day of period 3 is the last Gregorian calendar day of a quarter. For FY2016, here's how it looks:

Quarter,Period,Start Day,Start Date,End Day,End Date,Nb Days

FY16-Q1,FY16-01,Thu,01/10/2015,Sat,31/10/2015,31

FY16-Q1,FY16-02,Sun,01/11/2015,Sat,28/11/2015,28

FY16-Q1,FY16-03,Sun,29/11/2015,Thu,31/12/2015,33

FY16-Q2,FY16-04,Fri,01/01/2016,Sat,30/01/2016,30

FY16-Q2,FY16-05,Sun,31/01/2016,Sat,27/02/2016,28

FY16-Q2,FY16-06,Sun,28/02/2016,Thu,31/03/2016,33

FY16-Q3,FY16-07,Fri,01/04/2016,Sat,30/04/2016,30

FY16-Q3,FY16-08,Sun,01/05/2016,Sat,28/05/2016,28

FY16-Q3,FY16-09,Sun,29/05/2016,Thu,30/06/2016,33

FY16-Q4,FY16-10,Fri,01/07/2016,Sat,30/07/2016,30

FY16-Q4,FY16-11,Sun,31/07/2016,Sat,27/08/2016,28

FY16-Q4,FY16-12,Sun,28/08/2016,Fri,30/09/2016,34

Interesting, isn't it?

[martinpedersen]

Hi

Another approach is to create the calendar from year and then looping down to dates. Instead of starting with the dates.

[#7]

I've been looking for this post for a long time, thanks a ton.

How do we handle the occasional 6 weeks in Dec problem in this? For such years, say for example the last Saturday of Dec may not be the Year end. Please give your suggestions.

[martinpedersen]

Hi Gamavidy,

I take a different approach using autogenerate to to create the years, then adding the periods and then adding the weeks.

In this example I add a extra week in period 12 every five year from the base-year:

You can change the expression "If(frac((AccYear-$(vStartYear))/$(vYrExtraWeek))=0 and AccPeriod=12, 1, 0)", if you want to follow another pattern for the extra week.

Eg.

Change "Let vYrExtraWeek = 5; " to "Let vYrExtraWeek = 6;" if you want the extra week every 6. year instead.

Change "AccPeriod=12," to "AccPeriod=4" if you want the extra week to be added in the 4. period instead.

etc.

//Settings:

LET vStartDate = num(makedate(2005,08,28));

LET vStartYear = 2006;

LET vYears = 20;

LET vWeeks = 52;

LET vPeriods = 12;

Let vYrExtraWeek = 5;

AccYearTable:

Load

RowNo()                                                                        as %AccYearPeriodId,

RowNo() - if(RowNo()>12, (floor((RowNo()-1)/12)*12), 0)    as AccPeriod,

$(vStartYear) + floor((RowNo()-1)/12)                               as AccYear

Autogenerate $(vYears)*12;

LEFT JOIN

Load

%AccYearPeriodId,

AccYear & num(AccPeriod, '00')                                         as AccYearPeriod,

If(floor(AccPeriod/3)=AccPeriod/3, 1, 0)                               as AccPeriodType

Resident AccYearTable;

LEFT JOIN

Load

%AccYearPeriodId,

floor((AccPeriod-1)/3) + iterno () + (AccPeriod*(4))-(4)      as AccWeek,

RowNo()                                                                       as %AccYearWeekId,  

Iterno()                                                                       as %AccPeriodWeekId,

If(Iterno() =(4 +AccPeriodType +

     If(frac((AccYear-$(vStartYear))/$(vYrExtraWeek))=0 and AccPeriod=12, 1, 0))

, 1, 0)                                                                            as T.AccPeriodEndWeek

Resident AccYearTable while iterno() <= 4 +If(frac((AccYear-$(vStartYear))/$(vYrExtraWeek))=0 and AccPeriod=12, 1, 0);

LEFT JOIN

Load

%AccYearWeekId,

AccYear & num(AccWeek, '00')                                    as AccYearWeek

Resident AccYearTable;

LEFT JOIN

LOAD

%AccYearWeekId,

Num(Date($(vStartDate)+RowNo()-1))                               as %Date,

Date($(vStartDate)+RowNo()-1)                                         as Date,

Weekday(Date($(vStartDate)+RowNo()-1))                          as Weekday,

Iterno()                                                                            as AccWeekDayId,

RowNo()                                                                            as %AccDayId,

If(T.AccPeriodEndWeek=1 and Iterno()=7, 1, 0)                as T.AccPeriodEndDay,

If(%AccPeriodWeekId=1 and Iterno()=1, 1, 0)                     as T.AccPeriodStartDay

Resident AccYearTable while iterno()<=7;

[hic]

This is taken care of automatically in the scripts that I have supplied. Years with 53 weeks (roughly every 5th year) will have an additional week in the 12th month. I.e. the sequence will be 4-4-5-4-4-5-4-4-5-4-4-6

Similarly, the 4-5-4 year will have 4-5-5 in the last quarter, and the 5-4-4 will have 5-4-5.

HIC

[#10]

Thanks Henric.

How do we calculate MTD, QTD and all those To Date numbers if we are having more than 4 weeks in a month? The number of days will exceed 31 and we will not be able to create a date column.

[#11]

I am trying to understand the below table and the last line really confuses me. Shouldn't it be Aug 31st instead? If i assume that's Aug 31st, how did we arrive at Sep 4th as the nearest Sunday? Aug 28th could also be the nearest, am I missing something here?

Please clarify.

[hic]

About MTD and YTD: There is no principal difference from a standard situation. If you for instance use

   Sum ({$<WeekBased445.DayOfMonth={"<=$(=Max(WeekBased445.DayOfMonth))"}>} Amount)

then the chart will show all days below the selected DayOfMonth. (You will of course need to add the accumulation and the set analysis that clears other calendar fields.)

If you instead want to make the MTD calculation using today as cut-off day, you may need to use

   If(Date=Today(),DayOfMonth) as WeekBased445.DayOfMonthToday,

in the script, and

   Sum ({$<WeekBased445.DayOfMonth={"<=$(=Only({1} WeekBased445.DayOfMonthToday))"}>} Amount)

in your chart to pick out the current day number.

Concerning the year start. There is a typo - it should say "Closest to Sept 1st". I'll change it.

HIC