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

[#3]

Thank you Henric, I will try this.

[#4]

I have tried to build the exact same table just like your example, and i am running into issues on the Year Start. Sunday is the first day of the week for me and Jan 4th is in the first week of the Fiscal Year every year.

So i am using like this,

Set vCal_FD=6

Set vCal_RD=4

But the output of the script lags one day for the Year, Month and Week starts. I am trying to figure out whats missing.

[#5]

I had developed this entire functionality in Oracle PL/SQL for one of my clients. Nice to see that in QV scripting itself.

[Anonymous]

In the alternative... get the client to provide a calendar in Excel!

53-Week Year

The principal problem I have experienced with the 4-5-5/4-4-5 calendars is the comparison between periods where one period contains the "Week 53".  For most companies adopting this 4-5-4/4-4-5 calendar type the next 53-Week Year this will occur in 2017.

In the retail sector, there is a specific rule for the comparison of periods which requires the weeks to be shifted to provide comparability of the actual week "dates" to ensure that seasonal periods (Christmas, Thanksgiving, etc) are compared correctly.  I found two approaches to creating the comparison, which I posted:

Rolling 52 Week Comparison (53-Week Year Problem)

If you have any alternative solution, I'd be interested to know if this can be done more simply,

Please post your comments,

Nick Scott.

[#7]

Hello Henric,

The solution works good, however when there are selections made on other dimensions, the variables created at the script level does not return anything. Any suggestions on this will be of great help.

Thanks,

[hic]

Ganesh

Variables that are statically defined in the script are never affected by selections. So there must be something else which cause this problem.

I suggest you open a thread in the discussions area, and post a sample of the problem.

HIC

[pschmidt1973]

Hi Henric

With the 4,4,5 calendar I would like a week 53 to be created every 7 years instead of the roughly every 5th year.

Is this possible in the script?

Thanks, Paul

[a2dr37ao]

Hi Henric
First of all, I liked your post very much.
But, after read it many times, I have a question that I hope you can answer.
In "our" 4-4-5 Calendar, we decided start on Sundays, right?
So, I configured ours variables like this:
Set vCal_FD = 6;
LET vCal_RD = MakeDate(2021,09,01) - MakeDate(2021,12,31);
SET FirstWeekDay=6;

In my case, my dates start on 01/01/2021 and ends on 31/12/2021; so I espected that our algorithm starts on Sundays.
But this statement bellow makes our Calendar starts on Saturdays (29/08/2020) and no on Sundays
Let vStartDate = WeekStart(vRefDate-vCal_FD)+vCal_FD-1;

So, I decided to remove -1, but I have to answer if this is correct.
What you think about this?

[a2dr37ao]

Hi @hic 
First of all, I liked your post very much.
But, after read it many times, I have a question that I hope you can answer.
In "our" 4-4-5 Calendar, we decided start on Sundays, right?
So, I configured ours variables like this:
Set vCal_FD = 6;
LET vCal_RD = MakeDate(2021,09,01) - MakeDate(2021,12,31);
SET FirstWeekDay=6;

In my case, my dates start on 01/01/2021 and ends on 31/12/2021; so I espected that our algorithm starts on Sundays.
But this statement bellow makes our Calendar starts on Saturdays (29/08/2020) and no on Sundays
Let vStartDate = WeekStart(vRefDate-vCal_FD)+vCal_FD-1;

So, I decided to remove -1, but I have to answer if this is correct.
What you think about this?

[hgreen]

How would you add the "month end" date for each month? I have attempted to get this to work, however, I was only able to get the months with 4 weeks to show the date properly. The month with 5 weeks would not show the proper "month end" date.