Qlik Calendar Script

This is a standard script we use for creating a calendar via a resident load. It’s compatible with QlikView and Qlik Sense and it’s super simple to implement, all that is required is to paste this after the import of the data you want to build the calendar from.

Yes, Qlik Sense will automatically create a calendar for you, but why we use this is that our version includes fiscal year specific fields. As such, this is built for a financial year starting 1 July because that’s the standard date for the commencement of a new financial year in Australia.

Once the script has been copied and pasted, just update the variables (Table, Date and Source) as per the inline comments.

/*

Build a calendar table from selected primary table with month sorted in fiscal year order
To change the starting day of the week use "Set FirstWeekDay = 0;" where 0 = Monday, 1 = Tuesday, etc

Script compatible with both Qlik Sense and QlikView

*/


// Change variable values to match the source
Set Table = 'Calendar';		// Name of this calendar
Set Date = 'Date';			// Date field in source table
Set Source = 'Dates';		// Name of source table for resident load


[$(Table)]:
Load Distinct // Distinct is optional but recommended to improve performance

	[$(Date)]																																											As [$(Date)]
	, Day ( [$(Date)] )                                                                                                                        											As [Day]
	, Dual ( Month ( [$(Date)] ), If ( Month ( [$(Date)] ) > 6, Month ( [$(Date)] ) - 6, Month ( [$(Date)] ) + 6 ) )             														As [Month]
	, Year ( [$(Date)] )                                                                                                                         										As [Year]  
	, Dual ( Year ( AddMonths ( [$(Date)], - 6 ) ) & '/' & Year ( AddMonths ( [$(Date)], 6 ) ), Num ( Year ( AddMonths ( [$(Date)], - 6 ) ) & Year ( AddMonths ( [$(Date)], 6 ) ) ) )	As [Financial Year]
	, Dual ( Date ( MonthEnd ( [$(Date)] ), 'MMM-YY' ), Date( MonthEnd ( [$(Date)] ), 'MMM-YY' ) )                                                           	 						As [Month / Year]    
	, WeekDay ( [$(Date)] )                                                                                                                     										As [Week Day]
	, Week ( [$(Date)] )                                                                                                                        										As [Week]    
	, Num ( Ceil ( Month ( [$(Date)] ) / 3 ), 'Qtr 0')                                                                                                									As [Quarter]
	, Week( [$(Date)] + 14 )																																							As [Fortnight]

Resident [$(Source)];