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

# Mid-month actual + forecast

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

+

 Employee Week Actual Hours John Doe 1 Jane Doe 1 24 Peter Smith 1 40 Paula Smith 1 36 John Doe 2 42 Jane Doe 2 40 Peter Smith 2 16 Paula Smith 2 24

+

 Employee Week ETC John Doe 1 Jane Doe 1 40 Peter Smith 1 40 Paula Smith 1 32 John Doe 2 24 Jane Doe 2 32 Peter Smith 2 40 Paula Smith 2 24 John Doe 3 40 Jane Doe 3 32 Peter Smith 3 40 Paula Smith 3 24 John Doe 4 40 Jane Doe 4 40 Peter Smith 4 40 Paula Smith 4 40

=

 Employee Week 1 Week 2 Week 3 Week 4 Total John Doe 42 40 40 154 Jane Doe 24 40 32 40 136 Peter Smith 40 16 40 40 136 Paula Smith 36 24 24 40 124
• ###### 1. Re: Mid-month actual + forecast

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

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.