Need to do Employee turnover analysis

[#1]

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

[manas_bn]

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;

[#3]

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

[#4]

Review the funtions Networkdate, firstworkdate and lastworkdate

[#5]

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.

[danimelo1]

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