Qlik Community

Qlik Design Blog

All about product and Qlik solutions: scripting, data modeling, visual design, extensions, best practices, etc.

Recipe for a 4-4-5 Calendar

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.

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.

 

Yearly calendar.png

 

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.

 

Year Shift2.png

 

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:

 

Ref Date Table.png

 

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

17 Comments
Partner
Partner

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

0 Likes
2,639 Views

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

0 Likes
2,639 Views
Partner
Partner

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?

0 Likes
2,639 Views
martinpedersen
Contributor

Hi

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

Calendar.JPG

0 Likes
2,639 Views
Not applicable

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.

0 Likes
2,639 Views
martinpedersen
Contributor

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:

Calendar2.JPG

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;

0 Likes
2,639 Views

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

2,639 Views
Not applicable

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.

0 Likes
2,639 Views
Not applicable

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.

Ref Date Table.png

0 Likes
2,639 Views

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.

Shift of 445 year.png

HIC

0 Likes
2,639 Views
Not applicable

Thank you Henric, I will try this.

0 Likes
2,639 Views
Not applicable

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.

Fiscal Cal.JPG

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.

Fiscal 2013.JPGFiscal 2012.JPGScript.JPG

0 Likes
2,639 Views
Not applicable

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

0 Likes
2,639 Views
nick_scott
New Contributor III

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.

0 Likes
2,639 Views
Not applicable

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,

0 Likes
2,639 Views

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

0 Likes
2,639 Views
Partner
Partner

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

0 Likes
1,573 Views