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

# Resigned Headcount Calculation for Attrition Rate

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..

• ###### 1. Re: Resigned Headcount Calculation for Attrition Rate

Hello Lalitha,

Please provide a small sample workbook.  A bit of sample data in a Tableau Packaged workbook will enable many more users to engage and assist.

Packaged workbooks: when, why, how

Regards,

Patrick