2 Replies Latest reply on Nov 20, 2012 10:31 AM by Lance Leonard

    Mid-month actual + forecast

    Lance Leonard

      We are currently struggling to get the right calculations in place to support a utilization forecast report for our engineering teams. We have 3 separate sources we are blending - Employee List, Actual Hours, and ETC Hours. The Employee List is technically the primary datasource (we can get away with using ETC hours as the primary). Essentially, though, we need to blend the Actual and ETC hours to get a monthly forecast that changes as the month goes on (If week 2 is complete, we have two weeks of actuals and two weeks of ETCs - in a four week month). The row grand totals are what are incorrect. Sample workbook and data attached.

       

      Also, a secondary problem exists in calculating base hours based on weeks worked (if someone starts with the company mid-month). If we were to assume everyone had a base of 40/wk, that would be easy enough, but we need to do some calculations based on when they worked, and aggregate appropriately for the same row grand total issue above. We currently are trying to base the calculation on the null values in the Actual and ETC Hour datasources, but do have the ability to get hire/term dates included in the Employee List if that helps with the base hours calculations.

       

      Any help is greatly appreciated.

       

      Employee
      John Doe
      Jane Doe
      Peter Smith
      Paula Smith

      +

      EmployeeWeekActual Hours
      John Doe1
      Jane Doe124
      Peter Smith140
      Paula Smith136
      John Doe242
      Jane Doe240
      Peter Smith216
      Paula Smith224

      +

      EmployeeWeekETC
      John Doe1
      Jane Doe140
      Peter Smith140
      Paula Smith132
      John Doe224
      Jane Doe232
      Peter Smith240
      Paula Smith224
      John Doe340
      Jane Doe332
      Peter Smith340
      Paula Smith324
      John Doe440
      Jane Doe440
      Peter Smith440
      Paula Smith440

      =

      EmployeeWeek 1Week 2Week 3Week 4Total
      John Doe
      424040154
      Jane Doe24403240136
      Peter Smith40164040136
      Paula Smith36242440124
        • 1. Re: Mid-month actual + forecast
          Tracy Rodgers

          Hi Lance,

           

          The issue with the grand totals is unrelated to the data sources being blended. Grand totals in Tableau are not necessarily a sum of whatever is in the rows/columns. Instead, it uses whatever the aggregation is that is used on the measure. For example, Utilization is taking the grand total of Use Hours (144) and dividing that by the grand total of Base Hours (40), hence giving a percentage of 360%.

           

          In terms of the work hours issue, adding the hire/term dates would probably get you the desired outcome for working hours.

           

          Hope this helps!

           

          -Tracy

          • 2. Re: Mid-month actual + forecast
            Lance Leonard

            Thanks, Tracy. The aggregation/totals scenario is exactly what is tripping us up. If you look at the row total for "Use Hours" in the attached workbook, the totals are "incorrect" (144) based on expectations (136), but correct based on the calculation used. We first need some help in building that calculation for the proper aggregation of Use Hours. Then, we can determine the proper Base Hours using hire/term dates, although ideally, we'd like to base the calculation off available values. Once we have those aggregating properly, the utilization calculation will be a cinch.