Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Highlighted
arniewolff
New Contributor III

Headcount vs Budget with variance

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

Headcount.jpg

1 Solution

Accepted Solutions
Partner
Partner

Re: Headcount vs Budget with variance

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?

6 Replies
Partner
Partner

Re: Headcount vs Budget with variance

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?

arniewolff
New Contributor III

Re: Headcount vs Budget with variance

Thanks Christopher! Adding the Sum() corrected the budget expression and allowed the existing Diff and Var % expressions to work as well.

sbgipson7788
New Contributor II

Re: Headcount vs Budget with variance

hi arnie,

did you ever convert the data to a graph or chart?

thanks!

arniewolff
New Contributor III

Re: Headcount vs Budget with variance

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.

Headcount table.JPG

sbgipson7788
New Contributor II

Re: Headcount vs Budget with variance

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.

sbgipson7788
New Contributor II

Re: Headcount vs Budget with variance

Hey Arnie,

i created 4 version of the linear gauge. worked out great!

thx again!


Capture.JPG