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 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:
if(sum(Actual) >= sum(Target), 0, sum(Actual))
if(sum(Actual) > sum(Target), sum(Target), if(sum(Actual) < sum(Target), sum(Target) - sum(Actual), 0))
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:
if(sum(Actual) >= sum(Target), 0, sum(Actual)) / sum(Target)
if(sum(Actual) > sum(Target), sum(Target), if(sum(Actual) < sum(Target), sum(Target) - sum(Actual), 0)) / sum(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
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.
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:
Hope that this recipe gives you something that you can apply in your own applications.