The BSC information dashboards helps us monitor the success or failure of the company's initiatives to reach its objectives and we define this success by creating a target for each measurement. In the dashboard, we've added a series of alerts in the form of dots that only appear when the measurement is below target. For simplicity, we've defined all the targets to be ten percent YOY growth.
Tip
The dots are created using chr(9679).
You can get Unicode geometric shapes at http://www.alanwood.net/unicode/geometric_shapes.html.
Along with reaching our targets, we also should analyze the effect on the sales process using statistical process control. Like all measures, monthly sales naturally fluctuates beyond our control. Therefore, how do we differentiate between variations that are natural and those that are caused by a change in the sales process?
In his book, Understanding Variation: The Key to Managing Chaos, Donald Wheeler recommends using the XmR chart. The X stands for average and the mR for moving ranges. It is often used to analyze whether a process is under control or whether process improvement initiatives are successfully reducing process variability. For example, if we were to manufacture bolts, we would notice that each bolt's exact diameter would vary. Some variation is fine as long as the bolt still fits its corresponding screw. However, if the bolts' diameters vary so much so that many have to be scraped and remade, then we confront a costly problem. It is, therefore, important that we monitor the manufacturing process to determine whether its variation is under control. Stephen Redmond includes a recipe to create an XmR chart in QlikView in his book, QlikView for Developers Cookbook.
In the previous context, we assumed that a variation is unwanted and that the XmR charts help us eliminate it. However, we can also use it when we want the results of a process to vary. For example, we don't want our sales process to be a controlled process with a predictable result month-in, month-out; but rather, we hope for variation that indicates that our monthly sales average is increasing. We use the XmR chart to eliminate the noise of natural variation and confirm whether this is really happening.
The usage of the XmR chart in this context has been mastered by the Performance Measure Specialist, Stacey Barr (staceybarr.com). Her book, Practical Performance Measurement: Using the PuMP Blueprint for Fast, Easy and Engaging KPIs, helps companies adopt better performance measurement techniques, such as this version of the XmR chart.
The following chart shows the actual sales and its average, or central line, within a range where sales could naturally vary, or the natural process limit. Unlike rolling averages, the central line only changes under certain conditions. In the chart that results from the following exercise, we change the central line and the range under the following conditions:
- If a value is outside of the natural process limit
- If eight consecutive points lie either above or below the central line (we recalculate the central line and the process limit beginning with the point from which the streak began)
- If ten out of twelve points lie either above or below the central line (we recalculate the central line and the process limit beginning with the point from which the streak began)
The target that we include in the XmR chart is for the central line to reach. In this way, we can be sure that we've reached it due to real process improvement and not because of natural variation. The target is represented by a single dot in the chart:
To create an XmR chart, do the following:
- Let's create the following variables:
Variables
Label
Value
vPointsGreaterThanCLif($1=1,Above([Central Line]) > Below(Sales,0),RangeSum(Above([Central Line]) > Below(Sales,$(=$1-1)) ,$( $(=if($1=1 ,'=0' ,'vPointsGreaterThanCL($(=$1-1))' ) ) ) ))vPointsLessThanCLif($1=1,Above([Central Line]) < Below(Sales,0),RangeSum(Above([Central Line]) < Below(Sales,$(=$1-1)) ,$( $(=if($1=1 ,'=0' ,'vPointsGreaterThanCL($(=$1-1))' ) ) ) ))These variables count the number of points above or below the central line within a given set of points. In QlikView, a conditional expression that is true is equal to
-1. So, instead of usingif(Above([Central Line]) > Below(Sales,0),1,0), we just useAbove([Central Line]) > Below(Sales,0)and take care of the negative sign later in the chart.These variables also use a parameter so that we can count the number of points above or below the central line out of the next six, ten, twenty, or fifty points, and we will be able to use the same variable. We also want to avoid calling the variable for each individual point, like in the following code:
-sum($(vPointGreaterThanCL(1)) + $(vPointGreaterThanCL(2)) + $(vPointGreaterThanCL(3)) + $(vPointGreaterThanCL(4)))
Therefore, we make the variable recursive so that we can arrive at the same result as the previous code with only one call:
-sum($(vPointsGreaterThanCL(4)))
This one call will start by evaluating the point four rows down and then call itself to evaluate the point three rows down. It will continue this process until it reaches the current row.
As a final note, we have to be careful to also make the dollar-sign expansion in the recursive function recursive or else it will get stuck in an infinite loop of dollar-sign expansions and cause QlikView and, possibly, the computer to lock-up.
- Next, let's create the following combo chart:
Dimensions
Label
Value
Year-Month
Year-Month
Expressions
Label
Value
Sales
sum({$<_ActualFlag={1}>} [Net Sales USD])Central Line
//if one of the conditions is met then recalculate CL //check first row if(RowNo()=1 ,RangeAvg(Below(Sales,0,count(Total distinct {$<_ActualFlag={1}>} [Year-Month]))) ,if( //check if value outside process limit (Sales>above([True Max]) or Sales<above(Min)) or //check if next 8 values above or below CL ((RangeMax(Above([Central Line]) ,Below(Sales,0,8))=Above([Central Line]) or RangeMin(Above([Central Line]) ,Below(Sales,0,8))=Above([Central Line])) and count(Total distinct {$<_ActualFlag={1}>} [Year-Month])-RowNo()+1 >= 8) or //check if next 10 of 12 values above or below CL (-1*$(vPointsGreaterThanCL(12))>=10 or -1*$(vPointsLessThanCL(12))>=10) ,RangeAvg(Below(Sales,0,count(Total distinct {$<_ActualFlag={1}>} [Year-Month])-RowNo()+1)) //if none of the conditions are met then use previous //CL ,Above([Central Line]) ))Min
=[Central Line] - 2.66 * [Moving Range Average]
Max
=[True Max] - IF(Min<0,0,Min)
CL Target
if(RowNo()=1,RangeAvg(Below(Sales,0,count(Total distinct {$<_ActualFlag={1}>} [Year-Month])))*1.1,Above([CL Target]) )True Max
=[Central Line] + 2.66 * [Moving Range Average]
Moving Range Average
//if one of the conditions is met then recalculate MR //check first row if(RowNo()=1 //check first row ,RangeAvg(Below([Moving Range] ,0,count(Total distinct {$<_ActualFlag={1}>} [Year-Month]))),if(//check if value outside process limit (Sales>above([True Max]) or Sales<above(Min))or //check if next 8 values above or below CL ((RangeMax(Above([Central Line]) ,Below(Sales,0,8))=Above([Central Line])or RangeMin(Above([Central Line]) ,Below(Sales,0,8))=Above([Central Line])) and count(Total distinct {$<_ActualFlag={1}>} [Year-Month])-RowNo()+1 >= 8)or //check if next 10 of 12 values above or below CL (-1*$(vPointsGreaterThanCL(12))>=10or -1*$(vPointsLessThanCL(12))>=10),RangeAvg(Below([Moving Range],0,count(Total distinct {$<_ActualFlag={1}>} [Year-Month])-RowNo()+1)) //if none of the conditions are met then use previous //CL ,above([Moving Range Average])))Moving Range
fabs(Above(Sales)-Sales)
- In the Expressions tab, define Sales as Line and Symbol, Central Line as Line, Min as Bar, Max as Bar, and CL Target as Symbol in the Display Options section. For the rest of this expression, deselect all the Display Options and enable the Invisible option.
- Define the Background Color attribute expressions for Min as the following code:
IF(Min<0,ARGB(100,158,202,225),White())
- Define the Background Color attribute expressions for Max as the following code:
ARGB(100,158,202,225)
- Define the Background Color attribute expressions for CL Target as the following code:
if( max(Total [Year-Month]) = only([Year-Month]) ,black(),black(0)) - In the Style tab, enable the Stacked option in the Subtype section.
- In the Presentation tab, set the Bar Distance and Cluster Distance to 0 in the Bar Settings section.
- In the Colors tab, enable the colors accordingly.
The expressions for Moving Range Average and Moving Range are invisible, but they help us make cleaner calculations of the natural process limits, Min and Max. We could also have assigned the expressions to variables and used a dollar-sign expansion. However, we elect to use invisible expressions because they are visual when we export the chart to Excel; therefore, they make the chart easier to debug if we detect any anomaly.
Also, as we use stacked bars to draw the natural process limit's blue background, Max only calculates the distance between Min and itself. If Min is positive, then Max will not be equal to the actual maximum process limit. So, we use an invisible expression called True Max to evaluate whether any value is beyond the limit. Also, if Min is positive, it's background color is white so that only the area between the minimum and maximum limits is blue.
In Moving Range Average and Central Line, we check the three conditions that indicate the process has changed. If it has changed, then we recalculate these two variables from the point when a streak begins, so we have to be forward looking using the below() function. Also, when we do the recalculation, we do it over all the values from this point onward in the chart. We determine the exact number of values after this point using the following code as the third parameter in the below() function:
count(Total distinct {$<_ActualFlag={1}>} [Year-Month])-RowNo()+1)If we were to use a different dimension other than [Year-Month], we would replace it here.
Finally, the explanation for why we use the constant value, 2.66, to calculate the Min and True Max, and the conditions that indicate a process change can be found in Understanding Variation: The Key to Managing Chaos, Donald Wheeler. We can also find a XmR chart recipe in Stephen Redmond's QlikView for Developers Cookbook and get a different perspective on how to create one in QlikView. Also, a more detailed explanation about the design and purpose of this chart can be found in Stacey Barr's Practical Performance Measurement: Using the PuMP Blueprint for Fast, Easy and Engaging KPIs.