13 Replies Latest reply on Dec 6, 2016 7:29 AM by Jesse Narang

# Calculating attrition rate at the end of every quarter

Hi Everyone,

I have a data source that contains active and termed hires information. I am trying to calculate the attrition rate which is explained below:

Total number of people terminated in a given quarter/Total number of people that were active in the same quarter

Suppose we have to calculate attrition rate for Quarter 2. The beginning number of active people at the start of the quarter was 5. Over the course of the quarter, 6 people were hired and 2 left the company. Therefore, total number of people active at the end of the quarter = 14

Total number of people that left that quarter = 2

Attrition for 2nd quarter will be 2(Number of people that left in Q2)/Total Number of people that were active in Q2) * 100 = 2/14 * 100 = 14.28 %

I am having issues trying to articulate this in Tableau.

I have attached the workbook.(Version 9.3). I have two data sources in there. They are basically the same except I had to pivot one to calculate number of hires and terminations in a given quarter.

Any and all help will be appreciated

Best Regards,

Jesse

• ###### 1. Re: Calculating attrition rate at the end of every quarter

How you got 14?

Quarter 1: Begin and End 5

Quarter 2:

Quarter 1 End -5

Quarter Hired 6

Quarter 2 Left 2

Quarter 2 End: (5+6-2) 9

Am I missing anything?

• ###### 2. Re: Calculating attrition rate at the end of every quarter

Please post Excel how should be final numbers per your requirements for all quarters.

• ###### 3. Re: Calculating attrition rate at the end of every quarter

Hi Sherzodbek,

Sorry for the late reply. That number was supposed to be 11

So the way it's calculated is:

Quarter1: Begin and End 5
Quarter2: Total Number of Hires = 6
Quarter 2 Left: 2
Attrition for Quarter 2 = 2/(5+6) = 2/11

Quarter 2: Begin with 9 active people(Since 2 left the company in Q2)
Quarter 3: Total Number of Hires = 9
Quarter 3 Left: 1
Attrition for Quarter 3 = 1/(9 active beginning of Q3 + 9 new hires) = 1/18 and so on

Hope that helped!!

I have attached the excel also.

Best Regards,

Jesse

• ###### 5. Re: Calculating attrition rate at the end of every quarter

Hi Sherzodbek,

Is it possible to downgrade this workbook to V9.3 ??

Best Regards,

Jesse

• ###### 6. Re: Calculating attrition rate at the end of every quarter

Jesse

i am not infront of my pc now. When i get home, I will repost with 9.3 version. Sorry for that.

• ###### 7. Re: Calculating attrition rate at the end of every quarter

No problem.

Thanks and Best Regards,

Jesse

• ###### 8. Re: Calculating attrition rate at the end of every quarter

I am so sorry but I don't have 9.3 version, but i can post pictures and 10.0 version. Sherzod

• ###### 9. Re: Calculating attrition rate at the end of every quarter

Hi Sherzodbek,

Thanks for the response.

So I looked at the numbers and not all of them were what I was looking for.

2016 Q1 and 2016 Q2 are right but Q3 and Q4 are a little off. Following are the numbers that should be there:

 Attrirition for Q1 (0/5) Attrition for Q2 (2/11) Attrition for Q3 (1/18) Attrition for Q4 (2/23)

Therefore, the total employee down the table should look like

2016 Q1 - 0

2016 Q2 - 11

2016 Q3 - 18

2016 Q4 - 23

Best Regards,

Jesse

• ###### 10. Re: Calculating attrition rate at the end of every quarter

Jesse,

In order for me to calculate, there should so logic, but the way you are calculating is not the same in every quarter as far as i can see. Why don't you post your excel with formulas how you are getting those numbers and i will convert to Tableau?

• ###### 11. Re: Calculating attrition rate at the end of every quarter Sherzod

1 of 1 people found this helpful
• ###### 12. Re: Calculating attrition rate at the end of every quarter

Hi Sherzod,

Sorry for the late reply. I was on vacation and got back today. I will look at the screenshot and mirror that to see if it works for me too.

Thanks,

Jesse.

• ###### 13. Re: Calculating attrition rate at the end of every quarter

Hi Sherzod,

So I think the method works although I had a question - is it possible to have the values remain the same even if I exclude certain quarters ?

For example this is what the standard looks like before any exclusions. Now I excluded 2016 Q1, 2 and 3. This changes the value of 2016 Q4 to just 6 instead of the original 23. Is there any way to avoid this ??

Best Regards,

Jesse