Control charts are charts that plot some sort of quality measurement, for example number of defective goods in a batch or the weight of packets of food. The measurements are then plotted over time to see if there are any patterns. I won't go into too much more detail, because this is not the place for it (and I don't want to bore anyone that already knows it!).
You then draw "control limits" - lines above and below the average and then investigate any points that fall outside those lines, or meet some criteria. What those criteria are, and where to set the control lines are dependant on the quality control system you have in place, and the type of data etc. Lean Six-Sigma is perhaps the most famous set of rules for determining when to take action.
In general the rules are often things like:
- X number of points above the average line
- X number of points increasing each time.
So how do you highlight "X points in a row"?
Here is a Qlikview example document of a control chart that I have created using pretend data. The data is pretending to be measurements with a mean of 15 and SD of 3.
The first chart is a moving average chart. The moving average was also used to create thetolerance zones on the main control chart, which is the second one.
In the example, I have selected 3 rules to highlight -
1# Any points above / below the tolerance zones
2# X points the same side of the mean (shown as dots)
3# X consecutive points that are increasing or decreasing (shown as a change to the line colour)
The general idea though, could be applied to lots of patterns where you wish to highlight X consecutive points behaving in a certain way
For each pattern, to keep things simple, I start by using a cumulative expression in the chart that can be referenced else where, but is not displayed by unticking any of the "line / bar / symbol" options.
This expression does creates a tally of the number of consecutive points where the rule has been flouted, by using the above function and referencing itself:
=if(RowNo()=1 or [Data]> [Average],0,above([R2CUM])+([Data]<=[Average]))
ie:if(it's the first row or the rule is not broken, 0, otherwise take the above value from this row and add to it)
NB this creates a list of negative descending numbers where the rule has happened and 0 otherwise. eg, 0,0,0,-1,-2,-3,0,0,0,-1,-2,0,-1,-2,-3,-4,-5....
The main expression then checks this value (using range min / max and above/below, where appropriate) for all the points from itself, to x points ahead to see if it is in a consecutive chain
eg Rule#2 (below)
ie: if(the lowest value of the XPOINTS in the expression R2CUM after this value is less than XPOINTS, then display the data point)
ie, look ahead X points and if we are in a chain long enough, then the miminum value will be -X points or less.
Feel free to have a play, and better still turn my chart into table to see all the expressions working for yourself.
I hope this makes it clear, this was harder than I thought to explain in a blog! Please download the example and have a look for yourself.
Interesting thing I noticed: while playing with the Above() and below() expressions - they don't work if you include fields that are also dimensions in the same table (or chart). Anyone else noticed that?
Next week: Ideas for free typed blocks of text... and not a word cloud