topic Re: Need to do Employee turnover analysis in QlikView

Need to do Employee turnover analysis

Tue, 08 Oct 2013 20:01:14 GMT

Hello,

I have Employee table:

EmplID	Term Date	Hire Date
1	1/2/2013	1/2/2012
2	2/2/2013	1/2/2013
3	3/3/2013	10/2/2012
4	4/4/2013	3/1/2013
5	5/5/2013	4/2/2012
6	6/6/2013	5/1/2011
9		5/1/2011
12		5/1/2011
8		4/2/2012
11		4/2/2012
14		4/2/2012
7		6/2/2013
10		6/2/2013
13		6/2/2013

Based on those information, I need to do employee turnover analysis.

1) calculate Month Begin Headcount

2) calculate Month End Headcount

3) calculate Termed Empl

4) calculate turnover ratio: (Termed Empl)/( Month Begin Headcount + Month End Headcount)/2

Can you help?

Thank you very much in advance,

Dust

Re: Need to do Employee turnover analysis

manas_bn — Tue, 08 Oct 2013 22:41:10 GMT

Dust,

Have you had a look at this? - http://community.qlik.com/blogs/qlikviewdesignblog/2013/02/12/reference-dates

See if the below code helps you. I did the same thing as in the blog with your data:

load * Inline [
EmplID, TermDate, HireDate
1, 1/2/2013, 1/2/2012
2, 2/2/2013, 1/2/2013
3, 3/3/2013, 10/2/2012
4, 4/4/2013, 3/1/2013
5, 5/5/2013, 4/2/2012
6, 6/6/2013, 5/1/2011
9, , 5/1/2011
12, , 5/1/2011
8, , 4/2/2012
11, , 4/2/2012
14, , 4/2/2012
7, , 6/2/2013
10, , 6/2/2013
13, , 6/2/2013
];

FinalData:
NoConcatenate
load EmplID,
HireDate,
if(trim(TermDate)='',today(),TermDate) as TermDate,//if date is null, set it to today
if(trim(TermDate)='',0,1) as Term_Flag
Resident RawData;

Reference:
load EmplID,
Date(HireDate + IterNo()-1) as ReferenceDate
Resident FinalData
While IterNo() <= TermDate - HireDate + 1;

drop Table RawData;

Calendar:
load ReferenceDate,
monthname(ReferenceDate) as Month, year(ReferenceDate) as Year,MonthStart(ReferenceDate) as Monthstart, MonthEnd(ReferenceDate) as MonthEnd
Resident Reference;

Re: Need to do Employee turnover analysis

Wed, 09 Oct 2013 03:41:05 GMT

Thank you very much - Manas! Your suggestion is great. I am going to use your solution on my program. I will let you know if I can make it work.

Best regards,

Dust

Re: Need to do Employee turnover analysis

Wed, 09 Oct 2013 04:15:22 GMT

Review the funtions Networkdate, firstworkdate and lastworkdate

Re: Need to do Employee turnover analysis

Wed, 09 Oct 2013 04:28:01 GMT

Thank you - Edwin.

I need count anyone who worked on this month and who termed on this month to calculate turnover ratio. Holiday also need to include.

Re: Need to do Employee turnover analysis

danimelo1 — Wed, 13 Dec 2017 19:43:17 GMT

Could you do this? I am facing the same problem.