Back

Explore Courses Blog Tutorials Interview Questions
0 votes
2 views
in BI by (17.6k points)

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. 

1 Answer

0 votes
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 <- 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

Related questions

0 votes
1 answer
0 votes
1 answer
asked Jul 25, 2019 in BI by Vaibhav Ameta (17.6k points)
0 votes
1 answer
0 votes
1 answer
asked Jul 23, 2019 in BI by Vaibhav Ameta (17.6k points)
0 votes
1 answer

Browse Categories

...