1 2 Previous Next 16 Replies Latest reply on Oct 18, 2016 5:56 AM by Jason Bouligny

# Turnover Rate (Hiring Date and Terms Date)

Hello,

I am doing some turover rates in Tableau, but I am kind of stuck right now..

Can you help me?

I have that:

Emp ID     Emp Name     Employee Status     Hired Date     Terms Date

0512          Bob               Active                    02/04/13         Blank

4789          Marie            Terminated              01/07/13        03/27/13

7893          Jeff                Active                    04/20/13         Blank

3789          Britanny        Terminated              03/15/13         04/27/13

I would like to have the count for each month of 2013 of the number of Hired and Terminated:

Jan     Feb     March     April

H                             1         1          1          1

T                              0         0          1          1

Total Active EE         1          2          2          2

But I am not sure because how to do it because I can't really use the Term or Hire Date as a Ref.

Thank you!

• ###### 1. Re: Turnover Rate (Hiring Date and Terms Date)

Are you going to be doing this on a repeated basis? I ask because if it's a one off, you can reshape the data in Excel to have one date column. If you do this repeatedly, you can use custom SQL.

• ###### 2. Re: Turnover Rate (Hiring Date and Terms Date)

Regular Basis

• ###### 3. Re: Turnover Rate (Hiring Date and Terms Date)

Hello,

Anyone has an idea?

• ###### 4. Re: Turnover Rate (Hiring Date and Terms Date)

What if you created the following calculated fields...

Terminated Month

IIF([Employee Status]="Terminated",DATEPART('month',[Terms Date]),0)

Hire Month

DATEPART('month',[Hired Date])

These functions will give you the numeric value for the month, or a 0 if the employee status does not equal 'terminated'.

• ###### 5. Re: Turnover Rate (Hiring Date and Terms Date)

I see, thank you for your answer.

But it will not give me how many Active and Terminated Employee I had each months the last 12 months.

to have a full picture of what I had each months the last 12 months, and based on that calculate the turnover rate.

To be able to split it by department, execs, etc.

• ###### 6. Re: Turnover Rate (Hiring Date and Terms Date)

Hey,

So I have created some parameters for Starting Date and End Date (no clue how to do it in a different way..)

I only calculated so far: Prior Month, YTD and last 12 Months. I have to change the end and start date parameters every time I will run the report..

So now I have my count for each months (active, terminated and total of EE)

Formula for Total Active EE prior Month for ex:

if ([REHIRE_DT]<=[Start Range Prior Month] AND ([Start Range Prior Month]< [TERM_DATE] or ISNULL ([TERM_DATE]))) then '1' else '0' end

0 = 2000

1 = 978

Formula for Terminated EE prior Month for ex:

[TERM_DATE]<=[End Range] AND ([Start Range Prior Month]< [TERM_DATE])

0 = 1021

1 = 18

For the Turnover for the prior month for ex, I need to do # of Terminated / Avg Total of Active EE on this period.

Here: 18/978

But I can't divide 2 booleans together, especially because I want to divide the count for 1 (18 for # of Terminated) and the count of 1 for (978 for Total active EE).

How do you divide those 2 Booleans together please?

• ###### 7. Re: Turnover Rate (Hiring Date and Terms Date)

Anyone? :)

• ###### 8. Re: Turnover Rate (Hiring Date and Terms Date)

I wonder if attached cross join solution solves your issue?

To understand attached solution, look at data in Excel and result in Tableau to understand the used cross join query:

SELECT [data\$].[Emp ID],

[data\$].[Emp Name],

[data\$].[Employee Status],

[data\$].[Hired Date],

[data\$].[Terms Date],

[lookup\$].[MonthStart],

[lookup\$].[MonthEnd]

FROM [data\$], [lookup\$]

WHERE [data\$].[Hired Date] <= [lookup\$].[MonthEnd]

AND IIF(ISNULL([data\$].[Terms Date]), #12/31/2999#, [data\$].[Terms Date]) >= [lookup\$].[MonthStart]

AND NOW() >= [lookup\$].[MonthStart]

• ###### 9. Re: Turnover Rate (Hiring Date and Terms Date)

Hi,

Is this the only way to achieve this. I am stuck in a similar situation. I have a start date and a termination date.

Emp ID     Emp Name     Employee Status     Hired Date     Terms Date

0512          Bob               Active                    02/04/13         Blank

4789          Marie            Term Final               01/07/13        03/27/13

7893          Jeff                Active                    04/20/13         Blank

3789          Brit                Term Final              03/15/13         04/27/13

I would like to have the count (for each month) of the number of hired and terminated:

Jan     Feb     March     April

Hired                             1         1          1          1

Terminated                     0         0          1          1

Total Active Employees   1          2          2          2

Using the start date as the reference does not result in the right number of people terminated that month. How do I achieve it to be able to calculate Terminations/Running Emp Count to get Turnover %. I need to calculate Annual rolling turnover as well, but I think the first step is to get the terminations right. Look forward to support.

Thanks!

• ###### 10. Re: Turnover Rate (Hiring Date and Terms Date)

I have the same problem/need as meenu.mynam  Anyone have any ideas?

• ###### 11. Re: Turnover Rate (Hiring Date and Terms Date)

hey, any luck? i am stuck in the same situation.

• ###### 12. Re: Turnover Rate (Hiring Date and Terms Date)

Hi Yousuf,

Nopes, no luck yet. But we are now exploring a solution through a custom SQL query. Seems that is the only one. A colleague suggested domain completion and padding, but then later said that this can be achived through a custom SQL query. We are working on that option now….

Best,

Meenu

• ###### 13. Re: Turnover Rate (Hiring Date and Terms Date)

Hi Meenu,

Thanks! We actually came to the conclusion that we need to modify our custom SQL query (same boat as you). Our data needs to be more in a transactional format rather than a lookup table. Right now we are passing hire date and term date in one line. Here is a link if you are interested:

Yousuf

• ###### 14. Re: Turnover Rate (Hiring Date and Terms Date)

Thanks Yousuf. We were able to do it through a custom SQL query too. I will check the link, thanks for sharing it ☺

Best,

Meenu

1 2 Previous Next