article Calendars in Member Articles

Calendars

hic — Wed, 21 Sep 2022 17:04:33 GMT

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

Re: Calendars

robinrouleur — Tue, 05 Jul 2016 06:35:20 GMT

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

Re: Calendars

hic — Tue, 05 Jul 2016 08:06:36 GMT

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

Re: Calendars

robinrouleur — Tue, 05 Jul 2016 09:02:35 GMT

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

Re: Calendars

hic — Tue, 05 Jul 2016 09:54:17 GMT

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

Re: Calendars

joseph_morales — Wed, 06 Jul 2016 10:48:44 GMT

Thanks Henric, very useful Script!!

Re: Calendars

Anonymous — Thu, 07 Jul 2016 05:35:58 GMT

Thanks a lot!!! Much appreciated!!!

Re: Calendars

danansell42 — Thu, 07 Jul 2016 10:27:47 GMT

Very helpful script. Thanks

Re: Calendars

Anonymous — Thu, 14 Jul 2016 07:50:53 GMT

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

Re: Calendars

hic — Thu, 14 Jul 2016 10:26:43 GMT

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

Re: Calendars

ajsjoshua — Mon, 18 Jul 2016 07:33:37 GMT

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.

Re: Calendars

poooja — Mon, 04 Mar 2019 15:42:29 GMT

Hi Henric,

Hope you can help me on the Below.

My source has sales & budget data so it has order date, Transaction date and budget date. Please help me with some logic for the below scenario .

1. How to find 'Total Number of weeks in the current month'. Week starts from Monday.If the week has started already it should be considered as same week irrespective of the new month start. If the month starts on middle of the week that should consider as last week of the previous month.I am able to achieve but not as desired.

(Answer is: Jan 5 weeks,Feb 4 weeks,Mar 4 weeks,Apr 5 weeks, May 4 weeks,June 4 weeks, July 5 weeks,Aug 4 weeks,Sep 4 weeks,Oct 5 weeks,Nov 4 weeks,Dec 5 weeks,)

Result what i am getting is : Feb 5 weeks, March 6 weeks but it should be 1 week less because Feb 1 false under last week of January and march 1 falls under last week of Feb.

2. Max(OrderDate) falls in which week of the current month.
Ex: if OrderDate 3/1/2019 this is falls in 4th week of Feb.

I have attached the QVW. in the below link.

Total Number of-weeks in the current month

Thanks!

Pooja

Re: Calendars

jorgie — Tue, 07 Mar 2023 11:18:31 GMT

Hi to all!

Amazing script, but as a newbie in qlik world I would like some assistance about setting the dates.

I used the 445 week part, when I selected the year 2022 I got first date 28/08/2022 and last 26/08/2023.

What changes I should do? I want to use the 445 rule but year starts at Jan and finishes at Dec.

(if it has been answered already I am sorry but I didn't understand it obviously....)

Thank you for your time

Re: Calendars

steveshaw — Wed, 29 Apr 2026 16:42:06 GMT

Awesome, thank you.