3 Replies Latest reply on Mar 22, 2017 3:21 PM by Jamieson Christian

    Calculating Turnover From 2 Files: Rosters for 1st of year, Terminations

    Michael Laures

      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 HCSOPEOP
      201518%18%36%1125.556
      201640%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!

        • 1. Re: Calculating Turnover From 2 Files: Rosters for 1st of year, Terminations
          Jamieson Christian

          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!

          1 of 1 people found this helpful
          • 2. Re: Calculating Turnover From 2 Files: Rosters for 1st of year, Terminations
            Michael Laures

            This is great, thank you. Calculating turnover in Tableau is not as straightforward as I had hoped!

            • 3. Re: Calculating Turnover From 2 Files: Rosters for 1st of year, Terminations
              Jamieson Christian

              Michael,

               

              I'm glad it helped you. I think the moral, as is so often the case in Tableau, is that the complexity of the solution depends on the structure of your data.

               

              Oftentimes, data prep is half the battle. If you have your data already aggregated at the level of detail you need, and you have it organized in a way that Tableau can easily consume, then the solution can often be very easy.

               

              That said, the fact that you're trying to show many different KPIs that are somewhat independent of each other, in that they aren't related via a hierarchy of dimensional segmentation… well, that usually means you have to resort to Measure Names / Measure Values to put them together, and that's often a bit tedious in Tableau.

               

              Anyway, happy viz-ing!

              1 of 1 people found this helpful