I looked at your data and I can't tell what uniquely identifies an employee in the data so I can't give you an exact answer on how to do this. It's also not clear to me whether the sample employee is being hired multiple times or not, and if so how to identify that in the data. Also the definition of the Numerator and Denominator isn't clear.
In any case the key is that the month you want to show for headcount doesn't actually exist in the data. For example if someone his hired on 1994-01-01 and terminated on 1997-06-12 then they need to show up for all the intervening months, and if nobody was hired or terminated in a given month that month doesn't exist in the data so we need to pad that in.
These instructions assume that a given employee isn't hired multiple times, if that happens then there would need to be a modification (which would depend on how that is identified in the data):
- Create a table of Months up until some point far in the future.
- Connect to that table of Months and add a filter for Month <= DATETRUNC('month', TODAY()).
- Connect to the headcount data and create a calculated field for the Hire Month.
- Use a non-equijoin where Month >= Hire Month. This pads out the data for all months for each employee. If a given employee has multiple
- Connect to the termination data and create a calculated field for the Termination Month.
- Join the termination data to the Month+headcount data on the employee id
- filter the data on ISNULL([Termination Month]) OR [Month] <= [Termination Month]
- Create a Headcount calculated field with a value of 1 (This is assuming that staff count as headcount in their termination month)
- Create a Total Terms calculated field with a value of IF [Month] = [Termination Month] THEN 1 ELSE 0 END
Normally I'd then output this and then build the view in Tableau Desktop. Month would go as a dimension on Rows then Measure Values on Text with Measure Names on Columns, and the measures SUM(Headcount), SUM(Total Terms), etc. would go onto the Measure Values card.
Person Number uniquely identifies an employee in the data and there is Termination Date as well!
I need ETL workflow using Tableau PREP with:
1) Aggregated data of Monthly
2)New aggregated columns Attrition rate, Number of Employees in Each Age Group & Termination Rate , by Tableau PREP.
Please find the updated data and could you help in a ETL workflow using Tableau PREP.
Given what you've posted I can't help you., there are several issues and open questions:
1) The sample data is using RANDBETWEEN() for person number, this creates duplicates in the data, a nonsensical relationship between the headcount data & terminations data, and makes a mess of trying to validate anything because the Person Numbers change from one refresh to the next. Please post data sets that have a unique person number.
2) You haven't defined how the attrition rate should be computed based on the sample data.
3) You haven't defined how the termination rate should be computed based on the sample data.
4) There is no definition of how the Age Groups are supposed to be computed - is this the calendar age of the employee, time of service, or what? If so what is the algorithm for doing this?
5) Based on the #2 sentence it's not clear which of the measures (attrition rate, number of employees, termination rate) should be grouped by age group.
If you can answer some number of these questions then a volunteer here on the forums could help you out to get you started.
1)Removed duplicates in the data, by having Unique person number, as well some are common between Headcount & Terminations data.
2) Attrition rate:= (Number of leaves ÷ number of employees) x 100.
3)Termination rate: Suppose you lost 33 employees over the last 12 months out of an average workforce of 110. Divide 33 by 110 and multiply by 100 to find the employee turnover rate of 30 percent.
1) I still can’t work with this data, the Headcount data has multiple rows for the same person number.
3) In your original data the view was by month, but the termination rate is being computed across 12 months, is that correct?
Updated the data with Unique person number and In my original data the view was by month, but the termination rate is being computed across 12 months!
I need to have jump start on this to go in a right direction by having some ETL flow using Tableau PREP.
1 of 1 people found this helpful
Thanks for the updated data, here's a flow:
The order of operations was slightly different from what I'd previously described. Also note the several steps in the pink rectangle needed to compute the moving average in Prep since it doesn't have a moving average function at this time.
In addition I added a separate Output step right after the monthly counts were computed and then used that in this Tableau Desktop view where the moving averages are computed using table calculations:
The v2019.4.2 Tableau Prep flow, the Dates Excel spreadsheet used for the padding, and the v2019.4 Tableau workbook are all attached.