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?

Calculating employee turnover with R

2 Answers