4 views
in BI

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

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.

by (47.2k points)

You can reshape your data to count the 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\$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)