I'm working on showing variance to budget for headcount, hours and dollars. The headcount piece is difficult to show correctly due to way I'm calculating the values for headcount: actual is the distinct count of employee id while budget is the max headcount value by area (I don't want to enter employee id records into the budget so I enter a value by area, by day).
Using Aggr and Max, I can get the values to show correctly for each day and for multiple days, but the total, difference and variance don't calculate how I want them to. You can see 3 different attempts in the attached example. The first is a simple calculation using expression totals and doesn't give meaningful results. The second filters out null values from the budget and uses Sum of rows for the total - the difference works for a day but not multiple days and the variance doesn't work. In the third I attempted to fix the variance to adjust for zero values in the budget or actuals but that's not any better.
Any ideas on how I should attack this problem?
Arnie
I'm not sure if I have understood you correctly. However, based on your first attempt change the budget expression to this:
sum(Aggr(Max({<FactType={"Budget"}>} Headcount), Area))
and the Var % expressioin to:
=Diff/Actual
Does this solve your problem? Or have I misunderstood you?
I'm not sure if I have understood you correctly. However, based on your first attempt change the budget expression to this:
sum(Aggr(Max({<FactType={"Budget"}>} Headcount), Area))
and the Var % expressioin to:
=Diff/Actual
Does this solve your problem? Or have I misunderstood you?
Thanks Christopher! Adding the Sum() corrected the budget expression and allowed the existing Diff and Var % expressions to work as well.
hi arnie,
did you ever convert the data to a graph or chart?
thanks!
I pulled the data into a straight table and used a linear gauge to show performance vs plan visually. That seemed the best solution for our needs.
Hey Arnie,
ive been away & busy start of the year. But my team likes your linear gauge & wants me use that since what we have in excel isnt recreatable in QV yet.
Hey Arnie,
i created 4 version of the linear gauge. worked out great!
thx again!