Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello everyone!
I am building a dashboard of general HR statistics. Currently, I am totally stuck with the formulation of the Headcount.
Employees are temporary and work at varying time intervals. In other words, they have different hire dates and exit dates, they also could be hired in different areas every period. Currently the source of information is the Human Resources system, in such a way that the tables are updated automatically.
My main Table where is all the employee information :
[Employee_History]
LOAD text(maenomi) as ID,
Fullname,
Action#,
text(maenomi)&NoAccion as KeyEmployee,
date(floor(Date)) as ActionDate,
Type,
Business Unit,
City
FROM "Eikon_DW".dbo."historico_temporales" where Fecha >= '01/10/2017'
My calendar table is the following:
TempCalendar:
LOAD
Date(Date#('10/01/2017','MM/DD/YYYY') + IterNo() - 1) as TempDate
AutoGenerate 1 While Date(Date#('10/01/2017','MM/DD/YYYY')) + IterNo() -1 <= date(floor(monthend(addmonths(Today(),-1))), 'YYYYMMDD');
HeadCount_MasterCalendar:
Load
TempDate
,MonthName(TempDate) as MonthNameMC
,Month(TempDate) as Month
,Year(TempDate) as Year
,(Right(Year(TempDate+92),2)-1)&'-'& Right(Year(TempDate+92),2) as CropYear
,If(Month(TempDate)='Oct' or Month(TempDate)='Nov' or Month(TempDate)='Dec','Q1',
If(Month(TempDate)='Jan' or Month(TempDate)='Feb' or Month(TempDate)='Mar','Q2',
If(Month(TempDate)='Apr' or Month(TempDate)='May' or Month(TempDate)='Jun','Q3',
If(Month(TempDate)='Jul' or Month(TempDate)='Aug' or Month(TempDate)='Sep','Q4')))) AS [Crop Quarter]
resident TempCalendar;
DROP Table TempCalendar;
Some useful information:
- The Date / Date of action field is in which the information of both hire and exit dates can be found. The "Type" field specifies whether it is "ING" or "SAL". The type "ING" classifies the Actiondate field as Hire Date of the employee and "SAL" classifies the Exit date.
- We work with a fiscal year that starts every year on October 1st and ends on September 30th .
- We have limited the information coming from the system starting on October 1st, 2017.
- I tried to make an interval match with the following script but results are not correct:
Join([Employee_History])
LOAD
ID,
Date(Date#(ActionDate,'MM/DD/YYYY')) AS [Start Date]
resident [Employee_History]
where Tipo='ING' and ActionDate>'01/01/2017';
Join ([Employee_History])
LOAD
ID,
Date(If(IsNull(ActionDate), Today(), Date(Date#(ActionDate,'MM/DD/YYYY')))) AS [End Date]
resident [Employee_History]
where Tipo='SAL' and ActionDate>'01/01/2017' ;
Tab1:
LOAD
ID,
Date(Date#([Start Date],'MM/DD/YYYY')) as Date1,
Date(Date#([End Date],'MM/DD/YYYY')) as Date2
resident [historico_temporales];
MaxMin:
Load Max(Date2) as Max,
Min(Date1) as Min
Resident Tab1;
Let vMinDate = num(Peek('Min',0,'MaxMin'));
Let vMaxDate = num(Peek('Max',0,'MaxMin'));
Drop table MaxMin;
IntervalMatch:
IntervalMatch(Date)
LOAD Date1,
Date2
Resident Tab1;
I noted that the tab 1 is not working as expected because the dates range don´t have any correspondence with each other and not all “ING” / “SAL” types are being take into consideration. Also if I limit my data with the starting date 10.01.2017 I would cut off some registers leaving some employees with incomplete periods because the “ING” or “SAL” were performed before the initial date.
I read the post from Henric “Slow changing dimensions” https://community.qlik.com/t5/Qlik-Design-Blog/Slowly-Changing-Dimensions/ba-p/1464187 , I found it pretty interesting because is pretty similar to this situation, where employees in addition to the variable working time, also re-enter the company in different areas or Business Unit; however I´m pretty new with qlik and I cannot put all pieces together to achieve the expected result.
Can somebody help me with this issue or provide me some guidance about ideas of what should I try?
