Employee Retention

[Anonymous]

Hello all --

It has been requested to calculate employee retention. Retention is assumed as any employee who exists in one period after another period. 

The data we have is table with all the days an employee was active. In the first example, employee id 1 was active from 1/1 to 3/1. Employee ID 2 was active from 1/1 to 4/1

Employee ID	Reference Date
1	1/1/2016
1	2/1/2016
1	3/1/2016
2	1/1/2016
2	2/1/2016
2	3/1/2016
2	4/1/2016

Therefore retention on Jan to Feb would be 2, Feb to Mar would be 2, Mar to Apr would be 1. (This is an example, there will be multiple Employee IDs across these time frames).

There is a master calendar tied to Reference Date with Month/Qtr/Year fields.

How can I represent this in a QlikView chart over Month/Qtr/Year?

Output should be:

Month	Retention
Jan-2016	2
Feb-2016	2
March-2016	2
April-2016	1

or

Qtr	Retention
Q1	2
Q2	1

or

Year	Retention
2016	2

[its_anandrjs]

Find the attached and try with this way with Month,quarter and year fields to be added on the model

[Anonymous]

Hi Anand -

This gives me the distinct employee IDs that existed in a period, not whether or not they existed in a prior period. My example was trivial, but there are hundreds of employee IDs across days. Need to be able to track whether an employee ID exists from month to month, from qtr to qtr, and from year to year.

For example, we may start 2016 with 100 distinct employee IDs. On Jan 1 2017, only 56 of those 100 distinct employee IDs are still active. That's 56 employees retained out of 100. In the meantime, we will have added 44 new employees to fill the ones who left.

We are still then at 100 distinct employee IDs, but only 56 of those 100 are retained from the prior period.

Thanks!