We have a lot of QlikView documents that measure various kinds of business planning performance. The measures are usually a calculation of the difference between planned values and the actual values. Then the calculation is normalized by dividing it either by the planned or actual values so that the measure can be shown on a document as a percentage.
Key measures or key performance indicators (KPI) like that, if they are well chosen and well designed, give people an idea of how well a business process is performing. I believe that the measure can be even more useful if we add to it an indication of whether the measure has been getting “better” or getting “worse” over time.
Most of the performance measures we use are aggregation calculations that can be used with various dimensions like customer, product, or geographic region. When you show a measure like that in a line graph where the x-axis is time (for example, weeks or months) the line is usually jagged with up and down measures and maybe a “hump” or two.
The first thing you can do to help someone figure out if the measure is getting better or worse is to use a trendline. In a line graph, go to the chart properties and pick the expression where you’d like to see the trend, find the trendlines section of the expression dialog and click the Linear check box. Now, click OK and you should see that QlikView has drawn a straight line across your jagged line graph. The upward or downward slope of the line can give a person some idea of whether the measure has been getting better or worse.
Note: I usually modify the Presentation tab of the chart properties and set Trendline Width to a narrow line (maybe 0.5) so it doesn’t compete with the main graph line for attention.
If you stop reading this post at this point, then you’ve already picked up something useful.
Some users of these documents say that they would like the document to point out the areas where a particular measure is getting very bad so they know where to spend their time or resources. We can use a function already built in to QlikView to provide us with a number that would, if it was a trendline in a graph, be the slope of the line.
If the line is going uphill from left to right, then that would be a positive slope and if it is going downhill then that would be a negative slope. A flat, level line would have a slope of zero. I’m going to describe here how to build a calculated dimension for a chart that can show the 20% of things where a measure has been deteriorating the worst over time.
First, to simplify the description, imagine that you have taken the performance measure expression from your straight table and put it into a variable. We’ll call the variable vKPI.
The time dimension field in the example is PWEEK.
And we are going to use this calculated dimension in a straight table where it will be labeled 20% of Projects Where KPI Has Deteriorated the Most
The linest function is used to calculate the slope of the imaginary graph line.
The Rank function will help identify the 20% of worst performing PROJECTs.
Aggr is used to create an array of PROJECT values for our chart dimension. In this example, the higher values indicate a deteriorating measure but you can flip the calculation around if your logic is reversed.
=aggr(if((Rank(
linest_m(
aggr(
=$(vKPI)
,[PROJECT],PWEEK)
,PWEEK)
,1)-1) / Count(distinct total [PROJECT]) < 0.20, [PROJECT]), [PROJECT])
I know this is one of the more complex topics I’ve tackled in this blog but the result in a document that analyzes key performance indicators is valuable. In the documents where I’ve used this concept I usually provide a method for the user to choose what dimension field they would like to use, so it is not limited to PROJECT so the user could choose PRODUCT, SALESPERSON, or REGION, etc. And then for even more flexibility, I put the calculated dimension into a cycle group made up of other calculated dimensions that identify things that need attention.
In my next post, I’ll show how a related calculation could be used to color code items in a chart so that the items where performance has been getting worse are tagged with red and items where performance has been getting better are tagged with green.
