#24 📆 Canonical calendar in Qlik Sense
👨🎓 Wanna represent data across two kinds of dates? You definitely need to know Canonical Calendar!
Yesterday, Usama Waqar reached me out asking for help with some HR data. He wanted to represent employee turnover from Hire and Termination dates.
Dealing with Human Ressources in Qlik requires some basic to advanced data modeling technics. One of them is well known and described Canonical Calendar.
The aim is to have only one date field from several by adding a "type of date" attribute. this attribute will allow you to use it as a dimension or in set Analysis.
Create a new table from as many Resident as Date field you need to analyze and then link to your Master Calendar 🧙♂️
Are you interested in more tips and tricks about Human Ressources ? Drop a comment!
❓How To❓
The script is quite simple and you can link your general Calendar to the field "Date" to get Year, Month, ....
CanonicalDates:
LOAD HRID,
1 as AbsoluteCount,
DateofHire as Date,
'Hire' as DateType
Resident HR
Where len(DateofHire)>0;
Concatenate(CanonicalDates)
LOAD HRID,
1 as AbsoluteCount,
DateofTermination as Date,
'Exit' as DateType
Resident HR
Where len(DateofTermination)>0;
