1 Reply Latest reply on Jun 18, 2018 2:53 PM by Patrick A Van Der Hyde

    Resigned Headcount Calculation for Attrition Rate

    lalitha p

      Hello Everyone,

       

      I am trying to build a HR report in which i am trying to show Attrition rate monthly wise. I have my hiring data from 1999 but i have my attrition data from 2017 on wards.

       

      Here is my data file format:

       

      Type , Emp ID , Emp name , BU  ,  Capability  ,  Location ,  Grade ,  Hiredate  ,  Promotion Date  ,  Termination Date ,  Regretted ,  Role ....

       

      where Type includes whether the employee is active or inactive , Termination Date is empty if the employee is active.

       

      I am trying to calculate attrition rate like this:

       

      Attrition Rate:  (Voluntary Leavers / ( Opening HC + Closing HC) / 2 )

       

      Ideally Closing HC : (Opening HC + New Joinees  - Resignees ) of that month

       

      Imagine i have my 2016 Closing HC is 100 which is nothing but Opening HC of Jan 2017

      and say i have 4 ppl new joined in Jan 2017 and 2 resignees and 1 voluntary left in jan 2017

      so my calculation is:

      Closing HC  :  (100 + 4 -2) = 102

       

      so my Attrition rate will be : (2/(100+102)/2) = 2/(202/2) = 2/101 = 1.9% is my attrition rate

       

      this is how i am trying to calculate for my data from 2017 on wards.

       

      But i have got an issue with my data here, i couldnt get resignees information properly.

      I have my sheet designed like this in a test mode as a cross tab.

      year(Hire data) , qtr(hiredate) , month(hiredate) : and then measures opening HC , new joinees , resignees, Closign HC and then Attrition rate.

      Getting good with Opening HC , new joinees for first records but resignees are not coming properly and so bad data with Closing HC and then Opening HC too

      i feel resignees is related with Terminated date but my report is having hiredate i feel because of this it is causing issue with the data.

       

      Opening HC Calc:

       

      ZN(IF ATTR(YEAR([Hire Date])) = 2017 AND ATTR(MONTH([Hire Date])) = 1

      THEN

      [Jan HC Opening]    // This is i have fixed it for Jan 2017 Opening HC :: 100

      ELSE

      //RUNNING_SUM([Jan HC Opening])

      //+ [New Joinees] - [Resignees]

      LOOKUP([Closing HC],-1)

      END)

      and then i took table down for this..

       

      New Joiness:

      COUNTD(if type = 'ACTIVE' then empid end)

       

      But i really couldnt pick up resignees

       

      Resigness:

      COUNTD(if type = 'INACTIVE'  then empid end)  

      i tried this it didn't worked as hiredate has taken in the report

      then i took like this

       

      IF ATTR(YEAR([Hire Date])) < 2017 THEN 0   -- as i dont have attrition data till 2016

      ELSE

      (COUNTD({ FIXED YEAR([Termination Date]) , DATEPART('quarter',[Termination Date]) ,

                      MONTH([Termination Date]), DATEPART('day',[Termination Date]) :

          COUNTD( IF NOT ISNULL([Termination Date]) THEN [Empl ID] END )} 

      ))

      END

       

      But this also didnt work for me..

      As this didnt work for me i couldnt get Closing HC and then as i am using Closing HC my Opening HC is also going wrong..

       

      Can anyone please help me...