Qlik Community

QlikView Documents

Documents for QlikView related information.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.

Calendars

Employee
Employee

Calendars

A calendar is very useful when you want to link your data to different time periods, e.g. when you want to display your KPIs over different years or months. Often you only have one date and you just want to use a standard calendar. For this case, there are plenty of resources on this community. You can find a good overview on How to use - Master-Calendar and Date-Values..

If you have several date fields, you should most likely have several calendars defined in your script. See

Why You sometimes should Load a Master Table several times

Canonical Date

If you want to use a non standard calendar, like a fiscal calendar or a 4-4-5 calendar, the challenge becomes more difficult. See e.g. Fiscal Yearor Recipe for a 4-4-5 Calendar‌.


The script posted here will help you create a more complicated calendar. It has parametrized examples for the following calendars:

  • Standard Gregorian month-based calendar
  • Standard Gregorian week-based calendar
  • Standard Gregorian week-based calendar with broken weeks
    The first and last weeks of the year do not span the shift of the year and they do not necessarily have 7 days
  • Month-based fiscal calendar
    The Gregorian months are used as fiscal months, but January is not necessarily the first month of the fiscal year.
  • Week-based 4-4-4 calendar
    The fiscal year is divided into 13 months with 4 weeks each. Occasionally there is a 14th month with one week.
  • Week-based 4-4-5 calendar
    The fiscal year is divided into 4 quarters, each with 3 months. The first month of the quarter has 4 weeks, the second has 4 weeks, and the last has 5 weeks. Occasionally the last month of the year has an additional week.
  • Week-based 4-5-4 calendar
    The fiscal year is divided into 4 quarters, each with 3 months. The first month of the quarter has 4 weeks, the second has 5 weeks, and the last has 4 weeks. Occasionally the last month of the year has an additional week.
  • Week-based 5-4-4 calendar
    The fiscal year is divided into 4 quarters, each with 3 months. The first month of the quarter has 5 weeks, the second has 4 weeks, and the last has 4 weeks. Occasionally the last month of the year has an additional week.
  • Broadcast calendar
    The months are week-based and start on the first day of the week that contains the first day of the Gregorian month.

There are several parameters that you can use to configure your calendar: The first month of the year, the first day of the week and the first week of the week-based year.

The script is commented, so hopefully you can read it and understand it. You can paste it straight into an empty app and run it to evaluate how it works. If you want to use parts of it inside one of your own apps, you may need to rename some fields.

The script should work in both Qlik Sense and QlikView.

Good Luck!

HIC

Comments
robinrouleur
New Contributor III

Thanks Henric, very useful as always, one question - in the Broken weeks calendar line 263 can sometimes give a WeekStart result in the previous year.  Isn't the definition of broken weeks that the week breaks on the year boundary with short weeks either side, or am I missing something?

Here is line 263 and an example where I think it gives the wrong result:

Date(WeekStart(Date-$(vCal_FD))+$(vCal_FD))as WeekStart,

Date = 01-Jan-2015

vCal_FD = 0

WeekStart = 29-Dec-2014 when it should be 01-Jan-2015?

Regards

Robin

Employee
Employee

You are absolutely right. The "WeekStart" in the script however denotes the Sunday (or whichever first-day-of-week you use) that is the start of the seven-day week.So it does not always correspond to the week numbering.

If you instead want to find the start of the specific week number, you need to use something like

Date(RangeMax(WeekStart,YearStart)) as WeekNumberStart

Both these fields can be useful.

HIC

robinrouleur
New Contributor III

Thanks Henric, much appreciated.

QV12 now appears to support a lot of this functionality natively through the new variables FirstWeekDay, BrokenWeeks, ReferenceDay and FirstMonthOfYear.  From memory there were some early bugs but with SR4 it seems to be working correctly - using your definition of WeekStart that we have discussed.


Can you please confirm and if possible provide some clarification on the use of FirstMonthOfYear?  Eg I was expecting functions like QuarterName to respond to this variable but it does not seem to?

Robin

Employee
Employee

Yes, much of the functionality has been integrated into functions like Month(), YearStart() etc.The goal is to be able to create any calendar using the new fields-on-the-fly feature (See Qlik Sense – Date & Time).

However, we are not quite done yet. More functions are needed, and I wouldn't be surprised if there still are bugs. The feature is still fairly new.

The FirstMonthOfYear is a variable that should affect functions used to create month-based calendars. So, yes, in my opinion it should affect what QuarterName returns. I'll investigate.

HIC

joseph_morales
New Contributor III

Thanks Henric, very useful Script!!

aquariansp69
New Contributor

Thanks a lot!!! Much appreciated!!!

danansell42
Contributor III

Very helpful script. Thanks

lauracastagna
Contributor

Hi Henric,

our Fiscal Calendar Year begins on the 1st July.  I am working out the ReferenceDay starting from July 2015.  I understand that this variable defines when a (week-based) year starts. Monday is start of the week here.  so am I correct by my calculation for RD (Fiscalyear) being:-

MakeDate(2015,07,06) - MakeDate(2015,12,31).    Result being :-

let= vCal_RD = -161, vCal_FD = 0  (i.e. Monday)

henric, what about the data in days 1,2,3,4?  would they have been included in the prior year?

cheers

Employee
Employee

If your Fiscal year starts on July 1st, it sounds to me as if you use a Month-based fiscal calendar. Then you should not use the reference day at all. Instead, you should use the script on the tab "Month-based Fiscal".

However, if you want a week-based fiscal calendar that starts on the first Monday of July, then July 7th is the first day that always belongs to week 1. Hence you should use

Let= vCal_RD = MakeDate(2015,07,07) - MakeDate(2015,12,31) :

Let= vCal_FD = 0 ;

The first days of July will then belong to the previous fiscal year.

HIC

ajsjoshua
Contributor III

Hihic

Nice post

I want to concatenate these two tables

fact and fact1

And also i want only weeks and year that i fact table. https://community.qlik.com/message/1082955#1082955

Regards,

Joshua.

Version history
Revision #:
1 of 1
Last update:
‎07-04-2016 10:23 AM
Updated by:
Employee