Calculation of employee turnover using R

Question

so I'm trying to calculate employee turnover. What started as a seemingly easy task turned out to be a bit of a challenge (easily handle me, I'm a professional in HR).

I can find the counts and sums of the columns, but I am having problems using these values ​​in the calculations. I tried to communicate with the count, length and xtabs functions, but was unsuccessful. I think I can split the data into subsets, but I don't think the way is here.

Below I am trying to find

#Running_terminations <- 

should be (Termination per month 1) + (Termination per month 2) ... / # months

 #Running_headcount <- 

should be (Number per month 1) + (Number per month 2) ... / # months

 #Annual_turnover <- 

(Work endings / Running staff) * 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 

Thus, in the above examples, the current turnover as of March (3/31/2015) will look like this:

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

Running_headcount = (4 + 3 + 4) / 3 = 3.7 Please note: in the staffing table, only the status "Asset" is calculated

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

As soon as I get the basics, I would like to be able to calculate the turnover by gender or type or by gender and type.

Thanks so much for reading this far.

EDIT:

If this helps, here is how I do the calculation in the Table.

 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 the current current current rate from the beginning of the year and then multiplied it by 12.

I read a little more about calculating moving averages, and I found a user here offering 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 problem is that I can’t get him to classify things by the Due Date. Another example is that I want to make a two-axis chart to show contracts and new types of hiring on a monthly basis, but I can only get cumulative numbers and finish printing dots. How can I show it monthly?

Answer 1

You can change your data to calculate the number of actives and the number of completions 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.

Answer 2

Using basic functionality, you can use

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

in the same dataset that Kumar gave.
Now your results

 rslt["Active"] Active 3.666667 rslt["Termination"] Termination 1 turnover <- 100 / rslt["Active"]