Announcements
Sept. 27, 10AM ET Do more with Qlik, Insight Advisor – our intelligent AI-assistant in Qlik Sense: Register
cancel
Showing results for
Did you mean:
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

1 Solution

Accepted Solutions

I'm not sure if I  have understood you correctly. However, based on your first attempt change the budget expression to this:

and the Var % expressioin to:

=Diff/Actual

Does this solve your problem? Or have I misunderstood you?

6 Replies

I'm not sure if I  have understood you correctly. However, based on your first attempt change the budget expression to this:

and the Var % expressioin to:

=Diff/Actual

Does this solve your problem? Or have I misunderstood you?

Contributor III
Author

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

Contributor II

hi arnie,

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

thanks!

Contributor III
Author

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.

Contributor II

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.

Contributor II

Hey Arnie,

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

thx again!

Community Browser