In this article, I will share the ultimate Qlik Sense calendar script created using field definitions. The calendar consists of fields you may want to use for display purposes in visualisations and fields you may want to use for calculations. I will thoroughly explain what each section of the calendar is doing and provide some use-case examples. See the full table of contents below.
Let’s jump straight into it!
The calendar script
The code for the ultimate calendar is long and you will likely not need to use all the fields in every app so feel free to comment out or delete any irrelevant fields when using. Here is the code in full.
/*************************************************************/
//Ultimate custom calendar definition
/*************************************************************/
CustomCalendar:
DECLARE FIELD DEFINITION
Parameters
first_month_of_year = $(FirstMonthOfYear),
first_week_day = $(FirstWeekDay),
broken_weeks = $(BrokenWeeks),
reference_day = $(ReferenceDay)
Fields
//Fields for displaying
//Year fields
Year($1) as CalendarYear,
Year(YearName($1,0,first_month_of_year)) as Year,
YearStart($1,0,first_month_of_year) as YearStart,
YearEnd($1,0,first_month_of_year) as YearEnd,
//Quarter fields
'Q'&Ceil(Month(MonthStart($1,(first_month_of_year-1)*-1))/3) as QuarterNum,
QuarterName($1,0,first_month_of_year) as QuarterRange,
Dual('Q'&Ceil(Month(MonthStart($1,(first_month_of_year-1)*-1))/3)
&'-'&Year(YearName($1,0,first_month_of_year)),
Year(YearName($1,0,first_month_of_year))
&Num(Ceil(Month(MonthStart($1,(first_month_of_year-1)*-1))/3),'#,#00')) as QuarterYear,
Dual(Year(YearName($1,0,first_month_of_year))
&'-'&'Q'&Ceil(Month(MonthStart($1,(first_month_of_year-1)*-1))/3),
Year(YearName($1,0,first_month_of_year))
&Num(Ceil(Month(MonthStart($1,(first_month_of_year-1)*-1))/3),'#,#00')) as YearQuarter,
QuarterStart($1,0,first_month_of_year) as QuarterStart,
QuarterEnd($1,0,first_month_of_year) as QuarterEnd,
//Month fields
Month($1) as CalendarMonth,
Num(Month(MonthStart($1,(first_month_of_year-1)*-1)),'#,#00') as MonthNumber,
Dual(Month($1)&'-'&Year($1),Year($1)&Num(Month($1),'#,#00')) as CalendarMonthYear,
Dual(Year($1)&'-'&Month($1), Year($1)&Num(Month($1),'#,#00')) as CalendarYearMonth,
Dual(Num(Month(MonthStart($1,(first_month_of_year-1)*-1)),'#,#00')
&'-'&Year(YearName($1,0,first_month_of_year)),
Year(YearName($1,0,first_month_of_year))&
Num(Month(MonthStart($1,(first_month_of_year-1)*-1)),'#,#00')) as MonthNumberYear,
Dual(Year(YearName($1,0,first_month_of_year))
&'-'&Num(Month(MonthStart($1,(first_month_of_year-1)*-1)),'#,#00'),
Year(YearName($1,0,first_month_of_year))&
Num(Month(MonthStart($1,(first_month_of_year-1)*-1)),'#,#00')) as YearMonthNumber,
MonthStart($1) as MonthStart,
MonthEnd($1) as MonthEnd,
//Week fields
Dual('W'&Week(AddMonths($1,(first_month_of_year-1)*-1),first_week_day, broken_weeks, reference_day),
Week(AddMonths($1,(first_month_of_year-1)*-1),first_week_day, broken_weeks, reference_day)) as Week,
Dual('W'&Week(AddMonths($1,(first_month_of_year-1)*-1),first_week_day, broken_weeks, reference_day)
&'-'&Year(YearName($1,0,first_month_of_year)),
Year(YearName($1,0,first_month_of_year))&
Num(Week(AddMonths($1,(first_month_of_year-1)*-1),first_week_day, broken_weeks, reference_day),'#,#00')) as WeekYear,
Dual(Year(YearName($1,0,first_month_of_year))
&'-'&'W'&Week(AddMonths($1,(first_month_of_year-1)*-1),first_week_day, broken_weeks, reference_day),
Year(YearName($1,0,first_month_of_year))&
Num(Week(AddMonths($1,(first_month_of_year-1)*-1),first_week_day, broken_weeks, reference_day),'#,#00')) as YearWeek,
WeekStart($1,0,first_week_day) as WeekStart,
WeekEnd($1,0,first_week_day) as WeekEnd,
//Day fields
Date(Floor($1)) as Date,
Day($1) as Day,
DayNumberOfYear($1,first_month_of_year) as DayNumberInYear,
DayNumberOfQuarter($1,first_month_of_year) as DayNumberInQuarter,
Dual(Num(Day($1),'#,#00')&'-'&Month($1), Num(Month($1),'#,#00')&Num(Day($1),'#,#00')) as CalendarDayMonth,
Dual(Month($1)&'-'&Num(Day($1),'#,#00'), Num(Month($1),'#,#00')&Num(Day($1),'#,#00')) as CalendarMonthDay,
Dual(Num(Day($1),'#,#00')&'-'&Num(Month(MonthStart($1,(first_month_of_year-1)*-1)),'#,#00'),
Num(Month(MonthStart($1,(first_month_of_year-1)*-1)),'#,#00')&Num(Day($1),'#,#00')) as DayMonthNumber,
Dual(Num(Month(MonthStart($1,(first_month_of_year-1)*-1)),'#,#00')&'-'&Num(Day($1),'#,#00'),
Num(Month(MonthStart($1,(first_month_of_year-1)*-1)),'#,#00')&Num(Day($1),'#,#00')) as MonthNumberDay,
WeekDay($1,first_week_day) as DayOfWeek,
Dual(WeekDay($1,first_week_day)
&'-'&'W'&Week(AddMonths($1,(first_month_of_year-1)*-1),first_week_day, broken_weeks, reference_day),
Num(Week(AddMonths($1,(first_month_of_year-1)*-1),first_week_day, broken_weeks, reference_day),'#,#00')&
Num(WeekDay($1,first_week_day),'#,#00')) as DayOfWeekAndWeek,
Dual('W'&Week(AddMonths($1,(first_month_of_year-1)*-1),first_week_day, broken_weeks, reference_day)
&'-'&WeekDay($1,first_week_day),
Num(Week(AddMonths($1,(first_month_of_year-1)*-1),first_week_day, broken_weeks, reference_day),'#,#00')&
Num(WeekDay($1,first_week_day),'#,#00')) as WeekAndDayOfWeek,
Dual(Num(DayNumberOfYear($1,first_month_of_year),'#,#00')
&'-'&Year(YearName($1,0,first_month_of_year)),
Year(YearName($1,0,first_month_of_year))&Num(DayNumberOfYear($1,first_month_of_year),'#,000')) as DayNumberYear,
Dual(Year(YearName($1,0,first_month_of_year))
&'-'&Num(DayNumberOfYear($1,first_month_of_year),'#,#00'),
Year(YearName($1,0,first_month_of_year))&Num(DayNumberOfYear($1,first_month_of_year),'#,000')) as YearDayNumber,
Dual(Num(DayNumberOfQuarter($1,first_month_of_year),'#,#00')
&'-'&'Q'&Ceil(Month(MonthStart($1,(first_month_of_year-1)*-1))/3),
Ceil(Month(MonthStart($1,(first_month_of_year-1)*-1))/3)&
Num(DayNumberOfQuarter($1,first_month_of_year),'#,#00')) as DayNumberQuarter,
Dual('Q'&Ceil(Month(MonthStart($1,(first_month_of_year-1)*-1))/3)
&'-'&Num(DayNumberOfQuarter($1,first_month_of_year),'#,#00'),
Ceil(Month(MonthStart($1,(first_month_of_year-1)*-1))/3)&
Num(DayNumberOfQuarter($1,first_month_of_year),'#,#00')) as QuarterDayNumber,
//Time fields
Hour($1) as Hour,
Minute($1) as Minute,
Second($1) as Second,
TimeStamp(TimeStamp#(Trim(TimeStamp($1,'DD/MM/YYYY hh')),'DD/MM/YYYY hh'),'DD/MM/YYYY hh:mm:ss') as DateHour,
TimeStamp(TimeStamp#(Trim(TimeStamp($1,'DD/MM/YYYY hh:mm')),'DD/MM/YYYY hh:mm'),'DD/MM/YYYY hh:mm:ss') as DateHourMinute,
Dual(WeekDay($1,first_week_day)&'-'&Num(Hour($1),'#,#00'),
Num(WeekDay($1,first_week_day),'#,#00')&Num(Hour($1),'#,#00')) as DayOfWeekHour,
Dual(WeekDay($1,first_week_day)&'-'&Num(Hour($1),'#,#00')&':'&Num(Minute($1),'#,#00'),
Num(WeekDay($1,first_week_day),'#,#00')&Num(Hour($1),'#,#00')&Num(Minute($1),'#,#00')) as DayOfWeekHourMinute,
TimeStamp#(Trim(TimeStamp($1,'hh:mm')),'hh:mm') as HourMinute,
//Fields for set analysis/ calculations
//Year fields
InYear($1,Today(),0,first_month_of_year) as InCurrentYear,
InYear($1,Today(),-1,first_month_of_year) as InPreviousYear,
InYearToDate($1,Today(),0,first_month_of_year) as InCurrentYearToDate,
InYearToDate($1,Today(),-1,first_month_of_year) as InPreviousYearToDate,
//Quarter fields
InQuarter($1,Today(),0,first_month_of_year) as InCurrentQuarter,
InQuarter($1,Today(),-1,first_month_of_year) as InPreviousQuarter,
InQuarter($1,Today(),-4,first_month_of_year) as InCurrentQuarterLastYear,
InQuarterToDate($1,Today(),0,first_month_of_year) as InCurrentQuarterToDate,
InQuarterToDate($1,Today(),-1,first_month_of_year) as InPreviousQuarterToDate,
InQuarterToDate($1,Today(),-4,first_month_of_year) as InCurrentQuarterLastYearToDate,
//Month fields
InMonth($1,Today(),0) as InCurrentMonth,
InMonth($1,Today(),-1) as InPreviousMonth,
InMonth($1,Today(),-12) as InCurrentMonthLastYear,
InMonthToDate($1,Today(),0) as InCurrentMonthToDate,
InMonthToDate($1,Today(),-1) as InPreviousMonthToDate,
InMonthToDate($1,AddYears(Today(),-1),0) as InCurrentMonthLastYearToDate,
//Week fields
InWeek($1,Today(),0,first_week_day) as InCurrentWeek,
InWeek($1,Today(),-1,first_week_day) as InPreviousWeek,
InWeek($1,MakeWeekDate(Year(AddYears(Today(),-1)),Week(Today()),
WeekDay(Today(),first_week_day)),0,first_week_day) as InCurrentWeekLastYear,
InWeekToDate($1,Today(),0,first_week_day) as InCurrentWeekToDate,
InWeekToDate($1,Today(),-1,first_week_day) as InPreviousWeekToDate,
InWeekToDate($1,MakeWeekDate(Year(AddYears(Today(),-1)),Week(Today()),
WeekDay(Today(),first_week_day)),0,first_week_day) as InCurrentWeekLastYearToDate,
//Day fields
InDay($1,Today(),0) as InToday,
InDay($1,Today(),-1) as InYesterday,
InDay($1,AddYears(Today(),-1),0) as InTodayLastYear,
//Time fields
InDayToTime($1,Now(),0) as InTodayToTime,
InDayToTime($1,Now(),-1) as InYesterdayToTime,
InDayToTime($1,AddYears(Now(),-1),0) as InTodayLastYearToTime
;
Derive Fields From DateTime Using CustomCalendar;
The calendar code explanation
As I said, the code is extremely long! Let’s break it down chunk by chunk so we can all understand what’s going on in there.
The parameters
/*************************************************************/
//Ultimate custom calendar definition
/*************************************************************/
CustomCalendar:
DECLARE FIELD DEFINITION
Parameters
first_month_of_year = $(FirstMonthOfYear),
first_week_day = $(FirstWeekDay),
broken_weeks = $(BrokenWeeks),
reference_day = $(ReferenceDay)
This first part of the script allows you to define certain parameters which give Qlik important information about your business reporting practices. The parameters I included are:
- first_month_of_year
- first_week_day
- broken_weeks
- reference_day
Let’s take a look at each one in turn.
First_month_of_year
The first_month_of_year parameter is the month of the year your business considers the first. For example, if you work on a calendar date basis, January will likely be month one. However, if you work on a financial year basis, you may want to have the first month as April. By default the calendar is set to take the first month from the system variable FirstMonthOfYear, defined at the very beginning of the script. The only time you have to change that is when you want this calendar to calculate dates on a different basis than this variable is set to. The months should be declared numerically, ranging from one to twelve. For example, if you want the value of this parameter to be two while leaving the system variable to be one, just change the definition to:
first_month_of_year = 2
First_week_day
The first_week_day parameter defines when your week starts. For example, your week will likely start on Monday if you’re based in the UK. If you’re based in the US, you will likely want to start your week on Sunday. By default, the calendar is set to take the first day from the system variable FirstWeekDay, which is defined at the very beginning of the script. The days should be declared numerically, ranging from zero to six, with zero being a Monday. For example, if you want the value of this parameter to be Sunday while leaving the system variable as is, change the definition to:
first_week_day = 6
Broken_weeks
The parameter broken_weeks refers to whether week one of the year starts on the first calendar day of the year or whether week 1 starts only when there are at least ‘x’ days of the year in the week. The ‘x’ days are set using the parameter reference_day (see below). For example, assume that your year starts on January 1st and your week starts on Mondays. If you want to start the first week each year on January 1st, even if January 1st is a Sunday and there will only be one day in the first week, you will want to use broken weeks. This means that your broken_weeks should be set to 1 by the system variable BrokenWeeks OR by changing the calendar definition to broken_weeks = 1. If you want your week one to begin only once a specific number of January days fall into it, then set broken_weeks = 0 and adjust the reference_day accordingly.
Reference_day
The reference_day parameter allows you to specify how many of the year’s days must fall into the week for it to be classed as week one. This is applicable only if you do not want to use broken weeks. For example, assume that your year starts on January 1st and your week starts on Mondays. If you want to start the first week each year only once four January days are in that week, set broken_weeks = 0 and reference_day = 4. If January 1st falls on a Sunday, January 1st will not be classed as falling into week one. It will be classed as being in week 53 from the previous year.
The year fields for displaying
The next section of the calendar starts building fields useful for displaying dates in visualisations. The top section creates fields relating to the year of the date.
Fields
//Fields for displaying
//Year fields
Year($1) as CalendarYear,
Year(YearName($1,0,first_month_of_year)) as Year,
YearStart($1,0,first_month_of_year) as YearStart,
YearEnd($1,0,first_month_of_year) as YearEnd,
The dates we’re calculating here are:
- CalendarYear,
- Year,
- YearStart,
- YearEnd
The results of these fields, assuming the year starts in January, are as follows:
CalendarYear
CalendarYear calculates just that – the calendar year of the date. Even if you change the first_month_of_year parameter to be other than January, this CalendarYear field will return the year as it is if you were working with calendar dates.
Year
The Year field is the year given that month you input to be month one. For example, if your year starts in February, any dates in January will show the previous year in this field. Note that if you set your first_month_of_year to January, the result in this field is the same as in the CalendarYear field.
YearStart and YearEnd
The YearStart and YearEnd fields return the date corresponding to the start and end of the year for the input date, respectively, given the first month of your year.
The quarter fields for displaying
The next section of the calendar builds fields relating to displaying quarters of the year. Note that all the fields are fully responsive to the first_month_of_year parameter.
//Quarter fields
'Q'&Ceil(Month(MonthStart($1,(first_month_of_year-1)*-1))/3) as QuarterNum,
QuarterName($1,0,first_month_of_year) as QuarterRange,
Dual('Q'&Ceil(Month(MonthStart($1,(first_month_of_year-1)*-1))/3)
&'-'&Year(YearName($1,0,first_month_of_year)),
Year(YearName($1,0,first_month_of_year))
&Num(Ceil(Month(MonthStart($1,(first_month_of_year-1)*-1))/3),'#,#00')) as QuarterYear,
Dual(Year(YearName($1,0,first_month_of_year))
&'-'&'Q'&Ceil(Month(MonthStart($1,(first_month_of_year-1)*-1))/3),
Year(YearName($1,0,first_month_of_year))
&Num(Ceil(Month(MonthStart($1,(first_month_of_year-1)*-1))/3),'#,#00')) as YearQuarter,
QuarterStart($1,0,first_month_of_year) as QuarterStart,
QuarterEnd($1,0,first_month_of_year) as QuarterEnd,
Fields included are:
- QuarterNum
- QuarterRange
- QuarterYear
- YearQuarter
- QuarterStart
- QuarterEnd
The results of these fields, assuming the year starts in January, are as follows:
QuarterNum
The QuarterNum field returns which quarter the date falls into in the format of Q1-4. We account for a year starting at a month other than January by deducting the required months from the date before calculating the quarter. For example, if the year starts in February, we know that we need to shift the quarters back by one month so that January becomes part of Q4. Deducting one month from the definition date accomplishes this.
QuarterRange
The QuarterRange field returns the months the quarter starts and ends with, including the year.
QuarterYear and YearQuarter
QuarterYear and YearQuarter do the same thing but render the dates slightly differently. Both return the company year and company quarter for each given date. However, QuarterYear displays the dates in the format of Qx-YYYY, while the YearQuarter displays them as YYYY-Qx. You will likely not need both, which you use depends entirely on your preference.
QuarterStart and QuarterEnd
The QuarterStart and QuarterEnd fields return the date corresponding to the start and end of the quarter for the input date, respectively, given the first month of your year.
The month fields for displaying
The month fields for display are calculating the date formats relating to which month the date falls into. Note that there are some calendar month fields included.
//Month fields
Month($1) as CalendarMonth,
Num(Month(MonthStart($1,(first_month_of_year-1)*-1)),'#,#00') as MonthNumber,
Dual(Month($1)&'-'&Year($1),Year($1)&Num(Month($1),'#,#00')) as CalendarMonthYear,
Dual(Year($1)&'-'&Month($1), Year($1)&Num(Month($1),'#,#00')) as CalendarYearMonth,
Dual(Num(Month(MonthStart($1,(first_month_of_year-1)*-1)),'#,#00')
&'-'&Year(YearName($1,0,first_month_of_year)),
Year(YearName($1,0,first_month_of_year))&
Num(Month(MonthStart($1,(first_month_of_year-1)*-1)),'#,#00')) as MonthNumberYear,
Dual(Year(YearName($1,0,first_month_of_year))
&'-'&Num(Month(MonthStart($1,(first_month_of_year-1)*-1)),'#,#00'),
Year(YearName($1,0,first_month_of_year))&
Num(Month(MonthStart($1,(first_month_of_year-1)*-1)),'#,#00')) as YearMonthNumber,
MonthStart($1) as MonthStart,
MonthEnd($1) as MonthEnd,
Fields included are:
- CalendarMonth
- MonthNumber
- CalendarMonthYear
- CalendarYearMonth
- MonthNumberYear
- YearMonthNumber
- MonthStart
- MonthEnd
The results of these fields, assuming the year starts in January, are as follows:
CalendarMonth
The CalendarMonth field returns the calendar month of the date in the format of ‘MMM’. Even if you change the first_month_of_year parameter, the month will always show correct – January will always show as Jan.
MonthNumber
The MonthNumber field returns the number of the month and is impacted by the first_month_of_year parameter. If your first month is set as February, January will return the value of 12.
CalendarMonthYear and CalendarYearMonth
The fields CalendarMonthYear and CalendarYearMonth calculate the same but render the date slightly differently. In the CalendarMonthYear, the date is formatted as MMM-YYYY. In the CalendarYearMonth, the date is formatted as YYYY-MMM. Which you use depends on your preferences. Note that both fields calculate based on the calendar year even if you change the value of parameter first_month_of_year.
MonthNumberYear and YearMonthNumber
The fields MonthNumberYear and YearMonthNumber also do the same calculation but return results slightly differently. Use the one you prefer. The fields return the date formatted as a month-year and vice versa in a numerical format. These fields are responsive to the first_month_of_year parameter, so if your first month is February, January will be shown as number 12.
MonthStart and MonthEnd
The MonthStart and MonthEnd fields return the date corresponding to the start and end of the month for the input date, respectively.
The week fields for displaying
The week fields for display are calculating the date formats relating to which week of the year the date falls into. All fields respond to the relevant input parameters.
//Week fields
Dual('W'&Week(AddMonths($1,(first_month_of_year-1)*-1),first_week_day, broken_weeks, reference_day),
Week(AddMonths($1,(first_month_of_year-1)*-1),first_week_day, broken_weeks, reference_day)) as Week,
Dual('W'&Week(AddMonths($1,(first_month_of_year-1)*-1),first_week_day, broken_weeks, reference_day)
&'-'&Year(YearName($1,0,first_month_of_year)),
Year(YearName($1,0,first_month_of_year))&
Num(Week(AddMonths($1,(first_month_of_year-1)*-1),first_week_day, broken_weeks, reference_day),'#,#00')) as WeekYear,
Dual(Year(YearName($1,0,first_month_of_year))
&'-'&'W'&Week(AddMonths($1,(first_month_of_year-1)*-1),first_week_day, broken_weeks, reference_day),
Year(YearName($1,0,first_month_of_year))&
Num(Week(AddMonths($1,(first_month_of_year-1)*-1),first_week_day, broken_weeks, reference_day),'#,#00')) as YearWeek,
WeekStart($1,0,first_week_day) as WeekStart,
WeekEnd($1,0,first_week_day) as WeekEnd,
Fields included are:
- Week
- WeekYear
- YearWeek
- WeekStart
- WeekEnd
The results of these fields, assuming the year starts in January, the first weekday is a Monday, and we are using broken weeks, are as follows:
Week
The Week field returns the week’s number with a prefix of W. Remember, if you are using broken weeks, then the year and week one start on the 1st of January. This field is responsive to all the input parameters.
WeekYear and YearWeek
WeekYear and YearWeek fields calculate the same but render the date slightly differently. In the WeekYear, the week number is first and the year second. The YearWeek is the other way around. Both, the week and year part of the field are responsive to the input parameters.
WeekStart and WeekEnd
The WeekStart and WeekEnd fields return the date corresponding to the start and end of the week for the input date, respectively. The fields are responsive to the relevant input parameters.
The day fields for displaying
The day fields for display calculate the date formats relating to the actual date and day. There are a lot of fields here, so let’s explore them below.
//Day fields
Date(Floor($1)) as Date,
Day($1) as Day,
DayNumberOfYear($1,first_month_of_year) as DayNumberInYear,
DayNumberOfQuarter($1,first_month_of_year) as DayNumberInQuarter,
Dual(Num(Day($1),'#,#00')&'-'&Month($1), Num(Month($1),'#,#00')&Num(Day($1),'#,#00')) as CalendarDayMonth,
Dual(Month($1)&'-'&Num(Day($1),'#,#00'), Num(Month($1),'#,#00')&Num(Day($1),'#,#00')) as CalendarMonthDay,
Dual(Num(Day($1),'#,#00')&'-'&Num(Month(MonthStart($1,(first_month_of_year-1)*-1)),'#,#00'),
Num(Month(MonthStart($1,(first_month_of_year-1)*-1)),'#,#00')&Num(Day($1),'#,#00')) as DayMonthNumber,
Dual(Num(Month(MonthStart($1,(first_month_of_year-1)*-1)),'#,#00')&'-'&Num(Day($1),'#,#00'),
Num(Month(MonthStart($1,(first_month_of_year-1)*-1)),'#,#00')&Num(Day($1),'#,#00')) as MonthNumberDay,
WeekDay($1,first_week_day) as DayOfWeek,
Dual(WeekDay($1,first_week_day)
&'-'&'W'&Week(AddMonths($1,(first_month_of_year-1)*-1),first_week_day, broken_weeks, reference_day),
Num(Week(AddMonths($1,(first_month_of_year-1)*-1),first_week_day, broken_weeks, reference_day),'#,#00')&
Num(WeekDay($1,first_week_day),'#,#00')) as DayOfWeekAndWeek,
Dual('W'&Week(AddMonths($1,(first_month_of_year-1)*-1),first_week_day, broken_weeks, reference_day)
&'-'&WeekDay($1,first_week_day),
Num(Week(AddMonths($1,(first_month_of_year-1)*-1),first_week_day, broken_weeks, reference_day),'#,#00')&
Num(WeekDay($1,first_week_day),'#,#00')) as WeekAndDayOfWeek,
Dual(Num(DayNumberOfYear($1,first_month_of_year),'#,#00')
&'-'&Year(YearName($1,0,first_month_of_year)),
Year(YearName($1,0,first_month_of_year))&Num(DayNumberOfYear($1,first_month_of_year),'#,000')) as DayNumberYear,
Dual(Year(YearName($1,0,first_month_of_year))
&'-'&Num(DayNumberOfYear($1,first_month_of_year),'#,#00'),
Year(YearName($1,0,first_month_of_year))&Num(DayNumberOfYear($1,first_month_of_year),'#,000')) as YearDayNumber,
Dual(Num(DayNumberOfQuarter($1,first_month_of_year),'#,#00')
&'-'&'Q'&Ceil(Month(MonthStart($1,(first_month_of_year-1)*-1))/3),
Ceil(Month(MonthStart($1,(first_month_of_year-1)*-1))/3)&
Num(DayNumberOfQuarter($1,first_month_of_year),'#,#00')) as DayNumberQuarter,
Dual('Q'&Ceil(Month(MonthStart($1,(first_month_of_year-1)*-1))/3)
&'-'&Num(DayNumberOfQuarter($1,first_month_of_year),'#,#00'),
Ceil(Month(MonthStart($1,(first_month_of_year-1)*-1))/3)&
Num(DayNumberOfQuarter($1,first_month_of_year),'#,#00')) as QuarterDayNumber,
Fields included are:
- Date
- Day
- DayNumberInYear
- DayNumberInQuarter
- CalendarDayMonth
- CalendarMonthDay
- DayMonthNumber
- MonthNumberDay
- DayOfWeek
- DayOfWeekAndWeek
- WeekAndDayOfWeek
- DayNumberYear
- YearDayNumber
- DayNumberQuarter
- QuarterDayNumber
The results of these fields, assuming the year starts in January, the first weekday is a Monday, and we are using broken weeks, are as follows (note that some repetitive fields are excluded from the screenshot):
Date
The Date field trims any timestamp off the date and returns the date part only. You won’t need to use this function if you are working with dates without times.
Day, DayNumberInYear and DayNumberInQuarter
The Day field returns the day part of the date, i.e. 1-31. The DayNumberInYear field returns the number of the day in the year, i.e. 1-366. The DayNumberInQuarter returns the day number in the quarter, i.e. 1-92. As the Day function returns the day part of the date, it doesn’t change with any input parameters. However, the DayNumberInYear and DayNumberInQuarter are both responsive to the first_month_of_year input parameter.
CalendarDayMonth and CalendarMonthDay
CalendarDayMonth and CalendarMonthDay calculate the same value but return it in a slightly different format. Both fields return the day part of the date and the month in the ‘MMM’ format. As these fields return the month in the ‘MMM’ format, they do not respond to the input parameters.
DayMonthNumber and MonthNumberDay
These fields are very similar to CalendarDayMonth and CalendarMonthDay. The difference is that they return the month as a number, i.e. 1-12. For this reason, they are responsive to the first_month_of_year parameter.
DayOfWeek, DayOfWeekAndWeek and WeekAndDayOfWeek
The DayOfWeek field returns the day of the week in ‘DDD’ format, i.e. Mon – Sun. The DayOfWeekAndWeek and WeekAndDayOfWeek again calculate the same value but return it in a different order. One has the day of the week followed by the week number and the other has the week number first. All the fields are responsive to relevant parameters.
DayNumberYear and YearDayNumber
These functions return the day number of the year, i.e. 1-366, with the year. They are fully responsive to the input parameters.
DayNumberQuarter and QuarterDayNumber
These functions return the day number of the quarter, i.e. 1-92, with the year. They are fully responsive to the input parameters.
The time fields for displaying
The following fields are only helpful if you are working with timestamps you’d like to use in reporting.
//Time fields
Hour($1) as Hour,
Minute($1) as Minute,
Second($1) as Second,
TimeStamp(TimeStamp#(Trim(TimeStamp($1,'DD/MM/YYYY hh')),'DD/MM/YYYY hh'),'DD/MM/YYYY hh:mm:ss') as DateHour,
TimeStamp(TimeStamp#(Trim(TimeStamp($1,'DD/MM/YYYY hh:mm')),'DD/MM/YYYY hh:mm'),'DD/MM/YYYY hh:mm:ss') as DateHourMinute,
Dual(WeekDay($1,first_week_day)&'-'&Num(Hour($1),'#,#00'),
Num(WeekDay($1,first_week_day),'#,#00')&Num(Hour($1),'#,#00')) as DayOfWeekHour,
Dual(WeekDay($1,first_week_day)&'-'&Num(Hour($1),'#,#00')&':'&Num(Minute($1),'#,#00'),
Num(WeekDay($1,first_week_day),'#,#00')&Num(Hour($1),'#,#00')&Num(Minute($1),'#,#00')) as DayOfWeekHourMinute,
TimeStamp#(Trim(TimeStamp($1,'hh:mm')),'hh:mm') as HourMinute,
Fields included are:
- Hour
- Minute
- Second
- DateHour
- DateHourMinute
- DayOfWeekHour
- DayOfWeekHourMinute
- HourMinute
The results are as follows, assuming the first day of the week is a Monday:
Hour, Minute and Second
These fields return the time part of the timestamp as an integer.
DateHour and DateHourMinute
These two fields slightly trim the date time. The DateHour field returns the date with the time element only, while the DateHourMinute returns the date, hour and minute, trimming off the seconds.
DayOfWeekHour and DayOfWeekHourMinute
These two fields ignore the date and return the weekday in the format of ‘DDD’ and the time stamp. The DayOfWeekHour returns the hour only, while the DayOfWeekHourMinute returns the hour and minute.
HourMinute
The HourMinute field ignores the date and returns the time only in the format of hh:mm.
The year fields for set analysis
The fields created in the second part of the calendar script are designed to be used in calculations, such as inside set analysis. Each field contains only true (-1) or false (0). The year fields are set to check whether the date value falls into a specific year.
InYear($1,Today(),0,first_month_of_year) as InCurrentYear,
InYear($1,Today(),-1,first_month_of_year) as InPreviousYear,
InYearToDate($1,Today(),0,first_month_of_year) as InCurrentYearToDate,
InYearToDate($1,Today(),-1,first_month_of_year) as InPreviousYearToDate,
The fields included are:
- InCurrentYear
- InPreviousYear
- InCurrentYearToDate
- InPreviousYearToDate
The result of these calendar fields, assuming January is the first month of year, is as follows:
The InCurrentYear field returns -1 where the date falls into the current year. If it doesn’t, a 0 is returned. The field InPreviousYear does the same but where the date falls into the previous year. The fields InCurrentYearToDate and InPreviousYearToDate do the same thing but only return a 0 where the date falls before the specified date in the year. We specify today hence a -1 is returned where the date is before today and the same day last year, respectively. These fields are really useful for creating year-on-year type of analysis or KPIs. These fields are responsive to the first_month_of_year parameter.
The quarter, month and week fields for set analysis
The quarter, month and week fields are set to check whether the date value falls into a specific quarter, month or week, respectively. All the fields are responsive to the relevant input parameters.
//Quarter fields
InQuarter($1,Today(),0,first_month_of_year) as InCurrentQuarter,
InQuarter($1,Today(),-1,first_month_of_year) as InPreviousQuarter,
InQuarter($1,Today(),-4,first_month_of_year) as InCurrentQuarterLastYear,
InQuarterToDate($1,Today(),0,first_month_of_year) as InCurrentQuarterToDate,
InQuarterToDate($1,Today(),-1,first_month_of_year) as InPreviousQuarterToDate,
InQuarterToDate($1,Today(),-4,first_month_of_year) as InCurrentQuarterLastYearToDate,
//Month fields
InMonth($1,Today(),0) as InCurrentMonth,
InMonth($1,Today(),-1) as InPreviousMonth,
InMonth($1,Today(),-12) as InCurrentMonthLastYear,
InMonthToDate($1,Today(),0) as InCurrentMonthToDate,
InMonthToDate($1,Today(),-1) as InPreviousMonthToDate,
InMonthToDate($1,AddYears(Today(),-1),0) as InCurrentMonthLastYearToDate,
//Week fields
InWeek($1,Today(),0,first_week_day) as InCurrentWeek,
InWeek($1,Today(),-1,first_week_day) as InPreviousWeek,
InWeek($1,MakeWeekDate(Year(AddYears(Today(),-1)),Week(Today()),
WeekDay(Today(),first_week_day)),0,first_week_day) as InCurrentWeekLastYear,
InWeekToDate($1,Today(),0,first_week_day) as InCurrentWeekToDate,
InWeekToDate($1,Today(),-1,first_week_day) as InPreviousWeekToDate,
InWeekToDate($1,MakeWeekDate(Year(AddYears(Today(),-1)),Week(Today()),
WeekDay(Today(),first_week_day)),0,first_week_day) as InCurrentWeekLastYearToDate,
The fields included are:
- InCurrentQuarter/ InCurrentMonth/ InCurrentWeek
- InPreviousQuarter/ InPreviousMonth/ InPreviousWeek
- InCurrentQuarterLastYear/ InCurrentMonthLastYear/ InCurrentWeekLastYear
- InCurrentQuarterToDate/ InCurrentMonthToDate/ InCurrentWeekToDate
- InPreviousQuarterToDate/ InPreviousMonthToDate/ InPreviousWeekToDate
- InCurrentQuarterLastYearToDate/ InCurrentMonthLastYearToDate/ InCurrentWeekLastYearToDate
The results of these fields, assuming January is the first month of the year, are as follows:
The ‘InCurrentQuarter’, ‘InCurrentMonth’ and ‘InCurrentWeek’ all check whether the date falls into the current relevant period (quarter, month or week), even if the date is in the future. The fields ‘InCurrent…ToDate’ do the same thing but only check whether the date falls into the period up to the current date.
The fields ‘InPreviousQuarter’, ‘InPreviousMonth’ and ‘InPreviousWeek’ as well as ‘InPrevious…ToDate’ do the same thing, but check the previous period, not the one we are in currently.
Finally, the fields ‘InCurrent…LastYear’ and ‘InCurrent…LastYearToDate’ do the same thing as ‘InCurrent…’ and ‘InCurrent…ToDate’ but check last year rather than now.
The day and time fields for set analysis
These set analysis fields are only suitable if you want to analyse short-term metrics. The day fields return whether a given date is today, yesterday or today last year. The time metrics check the same periods but only return a true if the date times fall into today/ yesterday/ today last year to the current time. These fields are only relevant if you’re working with real-time data and are reloading the app multiple times throughout the day.
How to use the calendar script
To use the calendar script:
- Insert the code provided into the data load editor after the rest of your code
- Delete any calendar script lines calculating fields you don’t need
- Change the last line of code to refer to your date fields:
Derive Fields From "My Date 1", "My Date 2" Using CustomCalendar;
You can refer to a single date field or multiple fields by comma separating the field names as in the example code above.
Calendar script use case examples
Using date fields as alternative dimensions in a line chart
The date fields in the top half of the calendar script are ideal for use in visualisations. Here, we will show how to create a line chart with differing date views as alternative dimensions. Here is the example chart.
The chart shows the ‘Total Sales’ as a measure. We used the ‘Calendar Month Year’ date field for the main dimension. We used the ‘Quarter Year’ and ‘Year’ for alternative dimensions. The second image shows the chart after the user selects the ‘Year’ option. To achieve this:
- Create a line chart.
- Add the required measures.
- Add the date dimension you want to show as the default choice as the first dimension.
- Add alternative dimensions with date fields you want the user to be able to switch to.
Using date fields within a two-dimensional line chart
A two-dimensional line chart is where one dimension is used as the ‘x’ axis, and the second dimension is used to break the data down into different lines. Here are two example charts.
The first chart shows the sales by the ‘Calendar Month’ field with a different coloured line for each ‘Year’ field. The second chart shows the sales by the ‘Hour’ field with a different coloured line for each ‘Day of Week’ field. You can get really creative here to display your data in a way that makes the most sense for your users. To achieve this:
- Create a line chart.
- Add the required measure. Note that you can’t have multiple measures with this type of chart, BUT you can have alternative measures.
- Add the date dimension to show on the ‘x’ axis.
- Add a second date dimension to break the data down into different lines.
Using the boolean date fields in a table for comparative analysis
The date fields in the second half of the calendar script are ideal to use in calculations for comparative analysis. Here is the example table.
This table uses the ‘Calendar Month’ field to show the month in the first column. In column 2 it uses the field ‘InPreviousYearToDate’ in set analysis to sum the value of sales last year to date like this:
Sum({<[DateTime.CustomCalendar.InPreviousYearToDate] = {'-1'}>} Value)
The same thing is done in column 3, but the field ‘InCurrentYearToDate’ is used to get the current year’s sales. In column 4, we find the change between those two columns. This is a nice and simple but effective use of these calendar fields.
Using the boolean date fields in a KPI
Some users like to have a bunch of KPIs in a sheet to quickly understand what’s happening as soon as they open the sheet. Here is an example of a few KPIs you can easily create using set analysis with these boolean date fields.
Each of the KPIs uses the relevant boolean date field to calculate this period’s sales, e.g. this is the set analysis code to get this month’s sales:
Sum({<[DateTime.CustomCalendar.InCurrentMonthToDate] = {'-1'}>} Value)
The secondary KPI calculates the sales for the same period last year. For example, the month KPI checks the sales in the current month last year using the field ‘InCurrentMonthLastYearToDate’ in set analysis.
This concludes the explanation of the code and some basic use case examples. I hope you find it useful, and if you have any feedback or suggestions for other useful fields for future readers, please drop those in the comments below. Note that the Qlik help page on field definitions is found here if you would like to check it out for further help.
