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
.png "Former Employee"){kind=icon}
