0 Replies Latest reply: Feb 7, 2017 10:38 PM by Timothy Harris Caceres RSS

    Calculate Employee Attrition Rate for Rolling 12-Month (1 Year) Period

    Timothy Harris Caceres

      Hello - I am new to Qlickview, and trying to build a variable to calculate employee attrition but running into issues.  Any insights appreciated.  Thanks.

       

      Objective

      • Calculate Attrition Rate for 12 Month (1 Year) Period – Current Period (eg, through the Month ending Jan 2017) and Year Ago Period (eg, through the month ending Jan 2016) 
      • Period rolls forward with each successive month
      • Display the 12 month rate for the period (not the 12 month rate for each month).  In other words, the rate from the last month in the period back 12 months. 
      • To be used as a variable, with other dimensions (job type, etc).

       

      Calculations -

      Turnover Formula:  Turnover Rate = Total terminations / Average Headcount

      In Quikview:

      • Total Terminations for the 12 Month Period -- RangeSum(Above(total sum([Termination Count]),0,12))
      • Average Headcount for the 12 Month Period -- RangeAvg(Above(total sum([Ending Headcount]),0,12))
      • Term Rate - RangeSum(Above(total sum([Termination Count]),0,12))  /  RangeAvg(Above(total sum([Ending Headcount]),0,12))

       

      Problem

      • When I display both Record Year and Record Month, everything works.  Total Terminations, Average Headcount, and Term Rate yield correct results (verified against Excel spreadsheet).
      • When I click on the table to take Record Month out of the display, leaving just Record Year, which is what I am after, Total Terminations calculate correctly, but Average Headcount sums all the records in the data set rather than averaging the monthly sums.  Which of course throws off the Term Rate.

       

      Data Structured

       

      Record Month

      Record

      Year

      Record

      Type

      Dimension 1

      Dimension 2

      Ending Headcount

      Termination Count

       

      Jan

      2017

      Snap

      Group1

      Orgx

      1

       

       

      Jan

      2017

      Snap

       

       

      1

       

       

      Jan

      2017

      Snap

       

       

      1

       

       

      Jan

      2017

      Activity

       

       

       

      1

       

      Dec

      2016

      Snap

       

       

      1

       

       

      Dec

      2016

      Snap

       

       

      1

       

       

      Dec

      2016

      Snap

       

       

      1

       

       

      Dec

      2017

      Activity

       

       

       

      1

       

      Dec

      2016

      Activity

       

       

       

      1

       

      ….

       

       

       

       

       

       

       

      Feb

      2016

      Snap

       

       

      1

       

       

      Feb

      2016

      Snap

       

       

      1

       

       

      Feb

      2016

      Snap

       

       

      1

       

       

      Feb

      2016

      Activity

       

       

       

      1

       

      Feb

      2016

      Activity

       

       

       

      1

       

      Feb

      2016

      Activity

       

       

       

      1