There are a number of ways of showing variance to target in a dashboard. Here I show how to create a stacking bar chart which is simple to read and gives meaningful comparisons between dimensions and against target.
This recipe can be followed in either QlikView or Qlik Sense, and I will make available downloads for each via Qlik Community.
Load Some Data
This example will sit over any data where you have actual and target values loaded in against a common dimension. I have created some dummy data for my example using an INLINE load. This is a good way of getting test data fast:
ActualVsTarget:
LOAD
1 as RegionCount,
Region,
Target,
Floor(Target * Factor) as Actual
INLINE [
Region,Target,Factor
Northern,200,0.88
Southern,250,1.67
Eastern,150,1.33
Western,200,0.67
Midlands,225,1.14
];
Obviously in the real world there would be many more rows and dimensions, but this will suffice as an example.
Create a Chart and Add Your Dimension
In Sense you simply drag a bar chart onto your work space, in QlikView you can select a Chart from the top menu and make it a bar chart on the General tab. Adding the Region dimension is similarly a case of either dragging it across in Sense or selecting it from the list in QlikView.
If you are new to either of these tools there are plenty of getting started tutorials which you should probably refer to first.
The Expressions
The chart we are aiming for shows both the actual and target values in a single chart. Where the actual is less than the target it is a bar that is to the left, with the remainder of the target on top, and where the actual exceeds the target the full target value is shown as a bar with the remainder of the actual value on top.
In both cases two bars are shown, but three expressions are required are to do create this – one expression for each dimension will return zero. In the case of the actual exactly equalling target only the Over Target bar will show, with Under Target and Target both being zero.
The required expressions are:
Under Target
if(sum(Actual) >= sum(Target), 0, sum(Actual))Target
if(sum(Actual) > sum(Target), sum(Target),
if(sum(Actual) < sum(Target), sum(Target) - sum(Actual), 0))Over Target
if(sum(Actual) > sum(Target), sum(Actual) - sum(Target), 0)The way that the expressions are created in QlikView and Qlik Sense are different, but the syntax is identical.
The chances are that you will have some kind of set analysis in your expression, perhaps to show variance only for a certain period, or only to bring in actuals where there is a target, but this code suffices for our example.
Styling Your Chart
In order to get the chart we are after you will need to turn it into a stacked bar chart. In QlikView this is on the Style tab, and in Qlik Sense it’s under Appearance and Presentation. You will probably want to make the chart horizontal – as this is more logical for performance to a target. You will also want to ensure that your legends look how you want them to. We have a previous blog post on styling charts in QlikView that you may want to refer to.
Adding colours in QlikView is simple, as QlikView will apply the first three colours in the colour map to your three expressions. Use the GUI on the Colors tab to give you a chart that looks like this:
In Sense however there is no option to apply a different colour to each different expression on our chart. This, to my mind, is a major shortcoming and hopefully something that will be addressed in a future version. Later in this post I will give a work around for this, but for now our sense chart will look like this:
You will notice that at this point the hover text obtained by these charts is not ideal, as we have chunked the actual value into parts. In QlikView we can turn this off (under Presentation) but we have to live with it in Sense.
Variance as a Percentage
As well as showing absolute values we may want to see the variances as percentages. This is simply done by dividing each expression by the Target. The end of the Target bar will then sit at 1 (whether it is made up of one or two bars) and then things over target will extend beyond it.
The expressions for this second chart will read:
Under Target
if(sum(Actual) >= sum(Target), 0, sum(Actual))
/ sum(Target)Target
if(sum(Actual) > sum(Target), sum(Target),
if(sum(Actual) < sum(Target), sum(Target) - sum(Actual), 0))
/ sum(Target)Over Target
if(sum(Actual) > sum(Target), sum(Actual) - sum(Target), 0)
/ sum(Target)Note that you will need to change the Number format for all of the expressions. In QlikView this is done on the Number tab (note the percentage tick box at the bottom of the list) and in Sense it is under Data, Measures, Measure Name, Number Format.
Our Sense chart for percentages looks like this:
The combination of the two views can show differences between how the different regions are performing against target and against each other that the two charts in isolation might not make apparent. The other advantage of this pair of charts is that the percentage variance chart acts as a bit of a key to the other – making it perfectly transparent how the actuals relate to the target bars.
If you have been following this recipe in QlikView you should now have two charts that you can carry on and use. If however you are using Sense you have two charts with counter intuitive colours. There is a fix though…
Workaround For Bar Colours in Qlik Sense
Fortunately, this work around is no longer required for the colours in Sense. Make the expressions master items and then you can apply colours using master item colours. Text from original blog post left here though, in case it is useful to someone.
As mentioned above we can not select different colours or colour expressions for each expression in our Sense charts. We can however create a colour expression that refers to our dimensions.
One trick that can be used is to load a data island with the three bars in, so that we then have a bar chart with two dimensions. Instead of this we can create a second dimension in code using the ValueList function. Like the Aggr function this essentially returns a record set, but this time it is a recordset we have typed in.
Repeat the first couple of steps to get a new chart with a Region dimension on, then add a second dimension by using the Add Data button and inserting this code:
=ValueList('Under Target','Target','Over Target')Suggest putting a space character in the Label field, to suppress any other legend appearing.
Now, we can either have a chart with one dimension and multiple expressions, or two dimensions and a single expression (this is also true of QlikView). So, all three parts of our stacked bar need to be derived from a single expression, that reads like this and is again added using the Add Data button:
if(ValueList('Under Target','Target','Over Target') = 'Under Target',
if(sum(Actual) >= sum(Target), 0, sum(Actual)),
if(ValueList('Under Target','Target','Over Target') = 'Target',
if(sum(Actual) > sum(Target), sum(Target),
if(sum(Actual) < sum(Target), sum(Target) - sum(Actual), 0)),
if(ValueList('Under Target','Target','Over Target') = 'Over Target',
if(sum(Actual) > sum(Target), sum(Actual) - sum(Target), 0))))Set the legend for this new expression when you have finished. The outcome of this is a chart that is not dissimilar to our original Sense chart.
To apply the colour we need to go to the Appearance menu and select Colours and Legend. Here select By Expression from the drop down, and tick the The Expression Is A Colour Code tick box.
The following code is required in the function box that appears:
Pick(Match(
ValueList('Under Target','Target','Over Target'),
'Under Target', 'Target', 'Over Target'),
rgb(255,100,100), rgb(120,120,120), rgb(100,200,100))This uses the same ValueList function as we used for the calculated dimension. When we refer to it in the colour expression it will return the value for the wedge of the bar we are currently looking to colour. Also in this function is the very useful combo of Pick and Match, the Match function returning a value from 1 to 3 (depending on the current dimension) and pick selecting one of the three colours. You may well want slightly different colours – but you get the idea.
The same techniques, of using a calculated dimension and multiple expressions in one with an if statement, can be used in QlikView to solve situations where you want both multiple expressions and dimensions.
After this code has been applied our Sense chart should also look sensible:
Creating a percentage variance version of this chart is actually simpler than it was for our previous incarnation of the chart, as it doesn’t matter which part of the ValueList is being looked at, all results need to be divided by the target, so the expression for this will be:
if(ValueList('Under Target','Target','Over Target') = 'Under Target',
if(sum(Actual) >= sum(Target), 0, sum(Actual)),
if(ValueList('Under Target','Target','Over Target') = 'Target',
if(sum(Actual) > sum(Target), sum(Target),
if(sum(Actual) < sum(Target), sum(Target) - sum(Actual), 0)),
if(ValueList('Under Target','Target','Over Target') = 'Over Target',
if(sum(Actual) > sum(Target), sum(Actual) - sum(Target), 0))))
/ sum(Target)Again, the number format and legends will need to be amended to show the numbers in the right format and for it to be clear what the chart is showing.
Downloadable Examples
As with many of the examples that we have created, the apps that accompany this blog post can be downloaded from Qlik Community. There are versions for both Qlik Sense and QlikView as follows:
If you have found this article useful, and like the examples that are created, please take the time to give us some likes and comments on Qlik Community – this really helps get what we produce out to more people.
For a complete list of the applications we have shared please see this page:
https://www.quickintelligence.co.uk/examples/
Hope that this recipe gives you something that you can apply in your own applications.
Same logic as building a pie gauge.
http://www.qliktips.com/2013/12/key-performance-indicator-approaches.html
Hi Stephen, apologies… if I had remembered your post then I would have almost certainly referenced it. Apart from the fact that I am (obviously) not a pie fan, having the “actual” wedge being either to the left or the right of the vertical of the pie is a nice touch. Whilst having the under target values on the end of the target for the percentage bar chart in your article gives a nice butterfly chart effect, I would say generally that the red value should be on the axis, to enable easier comparison – particularly with the absolutes.
Your post points out also that I should have spent more time picking the grey for my Sense chart, definitely too dark on mine…
Thanks for commenting.
Hi Steve. Thanks for sharing such a detailed example. My problem with it is quite literally “the grey area” of the Absolute Difference To Target values. How do we distinguish on this chart values which exactly match Targets from dimensions with no value at all and therefore have no Under Target bar? I’ve elaborated on your example in my blog post to add “weight” to the rendered Target colour to distinguish between achieved and missed Targets. I’ve also added some contextual information to the graph regarding the variance.
https://www.linkedin.com/pulse/qlikview-hitting-targets-nikolas-daley
Hi Nikolas – Good spot – an extra colour is definitely required here. I have responded more fully on your post on LinkedIn, and recommend people go check this out.
Hi Steve.
Thanks for sharing.
In this example the bar chart finally looks like three transparent overlayed films with good bars behind and bad bars in front.
You can also blend “target gray” color with “good green” a little to make these bars on top. In this case
I find it easier to sense the data as the mind focuses on the whole values not the green deltas.
Hi,
Can you please tell me what exactly does the Factor field do?
Hi Michael,
This was simply used when generating the dummy data to give me some values that were above target and some that were below. It’s effectively the variance to target. Obviously in real life you will have actuals – rather than having to generate them.
Hope that makes sense?
Hi Steve.
This is great.
But i don’t see a bar if Actual = Target. Is there a workaround in that case?
Thanks
Satish
Hi Satish, yes you could expand this example further to colour the target in a green colour if it had been met, but grey if not met. You can either do this by adding a new section with a different colour, or by adding a colour expression to the Target bar (in QV) or modifying the colour expression in Sense.
For QV:
if(sum(Actual) >= sum(Target), rgb(180,240,180), rgb(200,200,200))
For Sense:
Pick(Match(
ValueList(‘Under Target’,’Target’,’Over Target’), ‘Under Target’, ‘Target’, ‘Over Target’),
rgb(255,100,100), if(sum(Actual) >= sum(Target), rgb(180,240,180), rgb(200,200,200)), rgb(100,200,100))
Note that if you use the colour expression in QlikView (rather than the extra section) then the legend will show just one box for the target and the colour of the legend will depend on whether the total amount is over or under target.
Thanks Steve
Hi all
In a larger data model (10Mio Plus rows in the Fact-Table) would you still use the “if” condition on front-end?
Have seen approaches with RangeMin / RangeMax to adress this issue.
What is your experience on performance?
Thanks
Hi Dominic,
The IF statements are around the outside of expressions, so to my mind would not be causing any great performance hit. Also, the repeated expressions will have cached results – so this will not hurt things.
Note the difference between:
if(sum(Value) > 0,
and
sum(if(Value > 0, Value, 0))
The second expression would be expensive in terms of processing, as opposed to Set Analysis to achieve the same, but the first would not be a problem.
I Have a Stacked bar chart and I want to change the color of each stacked bar, could you please help me.
Is this using QlikView or Qlik Sense? In QlikView you can do it by using the Color tab, the order of the colours on the palette there dictate the order of the colours on the stacked bar (unless persistent colours are used). You can also code colours by clicking on the Plus symbol on the expression and selecting Background Colour. It is not quite as straight forward in Sense, but I suspect it can be done.
Hi, Steve Dark
Please write how did you put space character in QlikSense label.
When I am trying to put space in label, it is always trimmed by Qliksense. As result I see an expression formula.
Looking at the app I produced for this post in Sense Desktop it has a space in the Field Label. Have you tried downloading the QVF from the link above? How does that behave when you open it?
I’ve just realised that the colours have gone horrible in Sense 3.2 for some reason – looks like I may need to amend the app anyway.
Hi Steve,
I am trying to display an actual vs a Target for Regions and Area’s in a Pivot table.
How do I go about that. I tried to use your example, but it does not work.
Here is the code I have used to try and display colors against the Target.
If((Sum(DeliveryAttemptInSLATimeCalc)/Sum([Consignment count])*100)>[SLA Target],RGB(242,28,23),RGB(52,179,20))
Hi Gerhardt. Is [SLA Target] a field in the data model? If so, is it possible that there is more than one value in the field, or null values? You could try putting a max around it, i.e. max([SLA Target]) to see if it works then?
Hello Steve, Nice approach andere solution. Ik figurered something out familiair, but it seems we both have a ‘problem’ in common. At least I think. If I want to show the user at all time the real value of the target value when Interacting with the chart, how do I accomplish this in Qlik Sense? Tot QV In would use the dual function. Do you have thoughts about this? Kind regards, cheers Eddie
As you say there are a few solutions in QlikView. In Sense I think you would have to have a separate chart. I would probably go for a table of values alongside the bar chart.
Thank you for this suggestion. I spend a lot of time to solve it in the same chart. This will do the trick. Thank you for your time and quick reply