Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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:
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
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
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
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
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
Thanks Henric, very useful Script!!
Thanks a lot!!! Much appreciated!!!
Very helpful script. Thanks
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
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
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.