From Date & To date under the same field. How to get active employees?
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 :
LOAD text(maenomi) as ID,
text(maenomi)&NoAccion as KeyEmployee,
date(floor(Date)) as ActionDate,
FROM "Eikon_DW".dbo."historico_temporales" where Fecha >= '01/10/2017'
My calendar table is the following:
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');
,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]
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:
Date(Date#(ActionDate,'MM/DD/YYYY')) AS [Start Date]
where Tipo='ING' and ActionDate>'01/01/2017';
Date(If(IsNull(ActionDate), Today(), Date(Date#(ActionDate,'MM/DD/YYYY')))) AS [End Date]
where Tipo='SAL' and ActionDate>'01/01/2017' ;
Date(Date#([Start Date],'MM/DD/YYYY')) as Date1,
Date(Date#([End Date],'MM/DD/YYYY')) as Date2
Load Max(Date2) as Max,
Min(Date1) as Min
Let vMinDate = num(Peek('Min',0,'MaxMin'));
Let vMaxDate = num(Peek('Max',0,'MaxMin'));
Drop table MaxMin;
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?