2 Replies Latest reply on Jul 10, 2014 12:11 PM by Robert Erich

    How Can I Drill Down the Date Without Changing FTEs

    Robert Erich

      I'm working on a project where I have employee hours and pay periods (every two weeks). However, I would like to observe FTEs (Full Time Equivalents) for a variety of time periods.

       

      One FTE is 80 hours per pay period (40 hours a week), or 2080 hours in a year (the equivalent of a regular, full time employee).

       

      To calculate annual FTEs, I can simply create the equation "total hours/2080". My issue comes when I try to drill down, because the equation for a quarterly FTE is not "total hours/2080", but "total hours/520".

       

      For example, if my total FTEs is 120 for the year, when I drill down to quarterly, Tableau says I have 30 FTEs per quarter. In reality, I still have 120 employees every quarter, they are just working for one quarter of the hours.

       

      Does anyone know a technique/formula/factor that I could use so that I can drill down from year to quarter to month without needing to use 3 different worksheets?

       

      Thanks for your response!

        • 1. Re: How Can I Drill Down the Date Without Changing FTEs
          Mark Holtz

          Could you utilize a parameter (Select Year, Quarter, Month/4weeks, or Week) to dictate what to divide by?

          i.e., set up a calculated field as

          IF [Parameter] = 'Year' THEN 40*52

          ELSEIF [Parameter] = 'Quarter' THEN 40*13

          ELSEIF [Parameter] = 'Month' THEN 40*4

          ELSEIF [Parameter] = 'Month' THEN 40

          END

          Then use your calculated field in your FTE calculation?

           

          You could also use your parameter to toggle between sheets a la this KB article:

          Creating a Sheet Selector for a Dashboard | Tableau Software

          1 of 1 people found this helpful
          • 2. Re: How Can I Drill Down the Date Without Changing FTEs
            Robert Erich

            Thanks for your response Mark!

             

            I actually started going down that direction and it wasn't panning out (particularly has 2 months every year have 3 pay periods).

             

            However, I found a way that worked:

             

            ( sum ( [Total Paid Hours] )  / CountD ( [PayPeriod] ) ) / 80

             

            I discovered that working from the bottom up was much more affective than trying to work from the top down.

             

            By dividing the total number of hours by the number of pay periods, when I drill down, the equation remains consistent because both hours and pay periods change proportionally.

             

            I had to include "Sum" before [Total Paid Hours] to get the Grand Total to work correctly.