Qlik Community

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
Read about the latest Qlik Community enhancements on the Community News blog!
cancel
Showing results for 
Search instead for 
Did you mean: 
sasoto92
Contributor
Contributor

From Date & To date under the same field. How to get active employees?

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.sasoto92_0-1627305759506.png
  • 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?

0 Replies