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.
Thank you for your answer.
Anyone has an idea?
What if you created the following calculated fields...
IIF([Employee Status]="Terminated",DATEPART('month',[Terms Date]),0)
These functions will give you the numeric value for the month, or a 0 if the employee status does not equal 'terminated'.
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.
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.
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?
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],
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]
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.
hey, any luck? i am stuck in the same situation.
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….
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:
Thanks Yousuf. We were able to do it through a custom SQL query too. I will check the link, thanks for sharing it ☺