I'm trying to calculate annual turnover from two files: one with rosters as-of the first of each year (in example, as of 1/1/2015, 1/1/2016, 1/1/2017), one with terminations (in example, terms for 2015-2016). I'd like the turnover to be calculated as (Average Headcount) / Terminations. So 2015 terminations would be ((2015-01-01HC + 2016-01-01HC) / 2) / (2015 terminations). Ideally, split by voluntary and involuntary terminations. So the results using Excel would be:
TurnoverTerminationsHeadcount VolInvTotalVolInvTotalAvg HCSOPEOP201518%18%36%1125.556201640%20%60%213564
I'm having trouble creating this in Tableau. I managed to create some sort of average headcount measure using Moving Average Table Calculation, but it isn't producing what I need, and I can't get the terms to split by year in the same table. I've attached a workbook with my sample data and what I have so far. Thank you for any help!
Michael,
Please see the attached workbook (version 10.1.6).
The following was done:
- Created Year fields in both data sources ([Rpt Year] and [Term Year]) so they could be used for blending.
- Created simple aggregations in the Termination table to sum up the voluntary, involuntary, and total terminations. This is necessary because you have no way of using [Term Type] as a dimension, because then it would have to map to something in the Roster table for the data blending to work.
- The above immediately tells us that we're going to have to use Measure Names / Measure Values in our view, to put all this together. So…
- Created a simple aggregation in the Roster table for Headcount SOP.
- Created a table calculation for Headcount EOP (which is really just Headcount SOP for the next year). This calculation utilizes LOOKUP to peek at adjacent cells.
- Created Average Headcount using Headcount SOP and Headcount EOP. Because it relies on Headcount EOP, which is a table calculation, Average Headcount will also behave as a table calculation.
- Finally, created calculations for Voluntary Turnover, Involuntary Turnover, and Total Turnover.
- Set appropriate default formatting for each, throw them all on the view along with [Rpt Year] for the Rows, and voila.
A couple notes:
- I also used a table calculation to filter out LAST()=0, which causes the latest year in the roster to not show.
- Adjusting the column header names to your liking has been left as an exercise for the reader. (Hint: set aliases on Measure Names.)
- Because we have to use Measure Names / Measure Values, you will have trouble developing a two-level hierarchy on your column headers (like you illustrated on your Excel mockup). You can accomplish something like it by putting the view into a Dashboard and then affixing additional text elements to simulate the top level of the header hierarchy.
I hope this helps!
