13 Replies Latest reply on Feb 6, 2018 11:17 AM by Caroline McDonald

    Calculation Help - Attrition Rates

    Caroline McDonald

      Hi -


      I've been struggling with a calculation for a while now and cannot figure out what I'm doing wrong.  I'm an HR professional and my role is focused specifically on analytics.  I'm trying to calculate attrition rates.


      Ideally, the way I would like to calculate this is below:


      # of terminations / average headcount


      A couple of notes: I need the number of terminations to be cumulative throughout the year.  This is the easy part - I can use a running total of number of terms that starts over each year.  The denominator is the part I'm having trouble with.  For this number, I need it to be an average headcount - take the headcount at the end of each month and average for the year.  The tricky part is that I need it to start over each year.  For example, monthly headcounts for January - December in 2017 would be added and divided by 12.  But now that we have started 2018, we will start over and add Jan + Feb/2, etc.


      I have two data sources - one file of terminations and one file of employees at each month.  I'm wondering if I need to use a fixed LOD calc?  We have several filters in our dashboard so that users can look at attrition rates for their teams - region, job title, month, year.  I want to make sure the numbers stay accurate even as different filters are applied.


      Please let me know if you have any ideas!