Calculating employee turnover with R

Question

so I'm trying to calculate employee turnover. What started as a seemingly easy task is proving to be a bit challenging (go easy on me, I'm an HR professional).

I can find counts and sums of the columns but I am having trouble using those values in calculations. I've tried messing around with the count, length, and xtabs function but have been unsuccessful. I think I can split the data into subsets but I don't think that's the way to go here.

Below is what I am trying to find

#Running_terminations <-

should be (Terminations in month 1) + (Terminations in month 2)... / # of months

#Running_headcount <-

should be (Headcount in month 1) + (Headcount in month 2)... / # of months

#Annual_turnover <-

(Running terminations / Running headcount) * 12

As Of      Status       Gender    Type
1/31/2015  Termination  Male      A
1/31/2015  Active       Female    A
1/31/2015  Active       Male      B
1/31/2015  Active       Female    B
1/31/2015  Active       Male      A
2/29/2015  Active       Female    A
2/29/2015  Active       Male      B
2/29/2015  Active       Female    B
2/29/2015  New Hire     Male      A
2/29/2015  Termination  Female    A
3/31/2015  Active       Male      B
3/31/2015  Active       Female    B
3/31/2015  Active       Male      A
3/31/2015  Termination  Female    A
3/31/2015  Active       Male      B

So in the sample data above the running turnover as of March (3/31/2015) would look as follows,

Running_terminations = (1 + 1 + 1) / 3 = 1

Running_headcount = (4 + 3 + 4) / 3 = 3.7 Note, only status "Active" are counted in the headcount

Annual_turnover = (1 / 3.7) * 100 = 27%

Once I get the basics out of the way, I would like to be able to calculate the turnover by gender, or type, or both by gender and type.

Many thanks for reading this far.

EDIT:

If it helps, this is how I do the calculation in Tableau.

Running Terminations (YTD) = zn(WINDOW_AVG((([Termination])),-11,0))
Running Headcount (YTD) = zn(WINDOW_AVG((([Active])),-11,0))
Annual Turnover (YTD) = (ZN(([Running Terminations])/[Running Headcount]))*12

So I first calculated a running YTD monthly turnover rate and then multiplied it by 12.

I did some more reading about calculating running averages and I found a user on here suggesting the following function.

ma <- function(x,n=5){filter(x,rep(1/n,n), sides=2)}

Now I am trying to apply this to my problem.

I think the main issue is that I cannot get it to categorize things by the 'As Of' date. Another example is that I want to make a dual axis plot to show terminations and new hires on a monthly basis but I can only get aggregate figures and end up plotting dots. How can I show this on a monthly basis?

Supererogate

Have you found cumsum yet? – Thermoluminescence

I have not, I've been trying to work on this using Kumar's advice. How can I use cumsum to get sums across the board by 'As Of' date? – Supererogate

Answer 1

You can reshape your data to count number of Active and Number of Terminations per month. Here is the code:

library(reshape2)
txt <- "As.Of      Status       Gender    Type
1/31/2015  Termination  Male      A
1/31/2015  Active       Female    A
1/31/2015  Active       Male      B
1/31/2015  Active       Female    B
1/31/2015  Active       Male      A
2/29/2015  Active       Female    A
2/29/2015  Active       Male      B
2/29/2015  Active       Female    B
2/29/2015  New_Hire     Male      A
2/29/2015  Termination  Female    A
3/31/2015  Active       Male      B
3/31/2015  Active       Female    B
3/31/2015  Active       Male      A
3/31/2015  Termination  Female    A
3/31/2015  Active       Male      B"

dataSet <- read.table(textConnection(txt), header=TRUE)
dataSet$As.Of <- as.Date(dataSet$As.Of, format="%m/%d/%y")
dataSet$As.Of.Month <- format(dataSet$As.Of, "%m")

dataSetAgg <- dcast(dataSet, As.Of.Month ~ Status, fun.aggregate = length, value.var="As.Of.Month")
Running_terminations <-  sum(dataSetAgg$Termination)/nrow(dataSetAgg)
Running_headcount  <- sum(dataSetAgg$Active)/nrow(dataSetAgg)
Annual_turnover <- (Running_terminations/Running_headcount)*100

Hope this helps.

Assertion

Many thanks! Will try this out tomorrow and reply back. – Supererogate

Sure. Happy to help. – Assertion

This has been extremely helpful and I've been able to apply this to my data. My issue now is that I would like to be able to plot the rolling headcount alongside the regular headcount (by month) and the solution you presented only gives me back a point. I've read about "ma <- function(x,n=5){filter(x,rep(1/n,n), sides=2)}" as a solution to rolling averages and I'm having some difficulty applying it to my case – Supererogate

I am going to consider this question answered and make a new post with a new question! Thanks again! – Supererogate

Answer 2

Using base functionality you can use

rslt <- table(dataSet$Status) / length(unique(dataSet$As.Of))  

on the same dataset given by Kumar.
Now your results are

rslt["Active"]  
 Active  
3.666667  

rslt["Termination"]  
 Termination  
1  

turnover <- 100 / rslt["Active"]  

Sociable