Turnover Calculation/ Beast Mode Help

user02149
user02149 Member
in Archive

I've got employee data coming in through our HRIS, and we've got a table that we link to, that provides our location hierarchy. I'd like to add a dynamic column to the merged data that gives me the current turnover for that location. The turnover formula we use is total number of terminations divided by the average number of employees at that location. (Ex. 6 employees have left the shop location, there is a current total of 12 employees at that shop, turnover is 50%).

 

If anyone has any suggestions for the best way to get this column into the data set, I would be very grateful. I think the turnover calculation needs to be placed on the location and not the employee to allow us to roll up the data through our hierarchy so we can show our market, region, and entire company's turnover. 

Comments

  • ST_-Superman-_
    ST_-Superman-_ Coach

    If you are wanting to look at this data rolled up to multiple location tiers (area, region, district, shop, etc.) I would recommend adding two fields at the employee level on your data set.

    1. `Terminations` - this would be 1 if the employee was terminated (or left?) and 0 if they were still there

    2. `Current Employees` - this would be 1 if the employee was still working (at that location?) and 0 otherwise.

     

    I'm not sure if you are wanting to track overall company turnover or at the location level.  If an employee transfers to another shop, should they be included in your turnover number?  

     

    Once these fields are added you can create a beastmode `Turnover %`

    case

    when sum(`Current Employees`)=0 then 0

    else sum(`Terminations`)/sum(`Current Employees`) 

    end

     

    This would give you the percentage whether you were grouping by shop, region, area, etc.  

     

    You could also use this as a summary number to give overall turnover rate.

     

    This will only work if your dataset only has one row of data per employee per shop


    “There is a superhero in all of us, we just need the courage to put on the cape.” -Superman
  • user02149
    user02149 Member

    Hi Scott,

    Thanks for the help! 

     

    We have a field in the current data set that returns either active or inactive called status. Every employee only has one line and they are either active or inactive. If someone transfers it doesn't count against the turnover rate. 

     

    Ideally, we'd track both at the location level and all the way through the company. Our shops all roll up through the location fields through markets, states, regions, and ultimately landing between two senior regional presidents. 

     

    Can you assist with the beast mode with these additional facts? 

  • ST_-Superman-_
    ST_-Superman-_ Coach

    case when

    sum(case when `status`='active' then 1 else 0 end)=0 then 0 else

    sum(case when `status`='inactive' then 1 else 0 end) / sum(case when `status`='active' then 1 else 0 end)

    end

     

     

    this should give you the number of "inactive" divided by the number of "active".  This is case sensitive so you may need to change it to "Active" or "ACTIVE" depending on your data.

     

    It will get grouped by what ever you are graphing by (location, etc.)


    “There is a superhero in all of us, we just need the courage to put on the cape.” -Superman
  • user02149
    user02149 Member

    Okay, this worked. Initially...

     

    However, I think that some of the data is really skewed because it's using a current count of employees or those marked as active instead of doing a historical snapshot of the average number of employees between the date range that I am wanting to view. So we are looking at 1/1-3/1 of 2018, let's say there have been 5 employees that have termed this year, and currently, we only have 3 employees marked as "active" however on average that shop over the ninety days probably had 6 employees... Is there a way to account for that with a different calculation? 


    It is not uncommon for us to hire/fire, techs in the same week so I'm thinking this is why I'm getting some areas with the turnover rate as way higher than when I calculate it in an excel sheet... 

  • ST_-Superman-_
    ST_-Superman-_ Coach

    hmm... I didn't understand the time component of this request.  Would you mind sending a sample version of your data set along with how you want the calculation to be done?  

     

    My guess is that you will need to do some field engineering within a data flow before this calculation can be done.  It would be helpful to have a clearer picture of the data set.


    “There is a superhero in all of us, we just need the courage to put on the cape.” -Superman
  • user02149
    user02149 Member

    Sure thing, thanks for all your help.

     

    So this is just an Excel with the raw data from two of our 500 locations. 

     

    The first tab has Palatine has a total of 19 lines, so 19 employees over all of time. However when we set the date range for 2018, it will be ytd or for this purpose, we can just say Jan. 1 2018 to March 1 2018. Based on the hire dates and term dates we can see,  on Jan 1 we had 12 active employees (column V) , on March 1 we had 12 active employees so the average number of employees here would be 12. The number of terms between Jan 1 and March 1 was, 1 so the calculation here would be 1/12 or ~8%.  

     

    The second tab with South Holland has an overall total of 42. 19 were active on 1/1/18, 19 active on 3/1/18, giving our average number of employees to be 19. The total number of terms between 1/1 and 3/1 is 2 so the turnover would be 2/19 or 11%. 

     

     

    Ideally, we'd have a snapshot from every day so we could actually know the average number of employees across time and not just using two endpoints but I'm not sure how to capture that either...

     

    It gets even hairier in that I'd like to be able to filter for titles but I think if I can get the calculations onto the dataset... the title part may be the easier step. Thank you again for all your help!!!

Categories