How to track movement between departments (calculate retention)

Hello,

In order to do a retention calculation I need to track the employees from month to month.

So let's say I want to find the retention for a department. I need to know who started in that department (at the beginning of the year) and if they are still in that department (at the time of the query).

So if there were 100 employees and now there are 90, obviously the headcount is down by 10 people. However, it could have been down by 20 people but 10 more were hired back in. So 20 people left that department.

If this calculation was just a headcount one, it would be easy to calculate. But I need to track each person.

Is there a methodology that anyone is aware of that would allow me to compare the beginning group to the current group and count who has left, by employee?

This can get much worse because my leadership asks for retention by department or manager or geographic area.

Thank you for the help.

-Gumbatman

If you are tracking just counts, no measures are associated, then one of the options:

Create snapshot for initial load of employees. This could be done just once, or yearly or in some cases even monthly.

For each change create record with -1 for exit and +1 for entry. This should be done in SQL server, so your loads into SSAS are simple.

That is if employee changed department, there is record for same day key for old department with -1 as measure and then +1 for new department.

Same rule if employee changes more than 1 attribute at the same time (department + manager) : record for exit with old attributes and record for entry with new attributes.

Sum of records from start snapshot to any day will give you who is in department that day.

This should be enough to track each employee.

Lets say you have 100 employees in HR. You create 20 exits, 10 entries. At the end you have sum of 90, all accounted. Plus, you can filter by any other attribute and have correct results.

Vidas Matelis

|||

Vidas,

That is a pretty cool method, I need to work through it a little more the get my head around it.

Thank you for the help!

-Gumbatman