Sunday, 24 March 2013

Qlikview Lean tips #1: How to highlight series of points that violate control rules on a chart

Highlighting patterns of points on a chart

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.
It's fairly easy to create a control chart with the limits, but a bit more complicated (judging from questions I get asked) to highlight every single point in a "run" that has violated the pattern, especially dynamically (as in not in the script). It's easier to just mark 1 (the start or end).
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?

Erica :)

Next week: Ideas for free typed blocks of text... and not a word cloud


  1. Nice Post. Thank's for Sharing!

  2. Thanks. Let me know if I've explained everything well enough!

  3. This is great. Have you created p-charts, u-charts and x-bar charts in Qlikview with rule firing capability? I would love to create those in Qlikview.. we use control charts extensively at my hospital.

  4. This is a great post. Thank you for sharing and look forward to your future posts.

  5. Thanks Tim. I've created p-charts but not with rules, are they generally any different to the above? Good idea for a future post I think!

    1. I have created a p-chart, i-chart and an xbar chart with the rules you have described. Yes they are all similar, although there are other rules i'd like to implement. Maybe we can collaorate.

    2. Cool, what kind of rules are you looking at?

  6. Hi Erica,

    It was a helpful post. In addition to what you have discussed, I am looking to create control charts where the control limits shift based on the trends ie., if there are 6 or 7 consecutive points on either side of the existing control limit, new limit should now be calculated with the new points. I have really no idea how to implement this. It would be great if you can help me with this stuff!!!!

  7. Great post, i am looking forward to implementing this.


  8. Hi Erica, Do you have any idea why for rule 1 and rule 2 it shows as a line when there is more than 100 dates displayed in the chart? For example if you filter for dates > than 8/2/13 it displays rule 1 and 2 as a line, if you then filter dates > 07/2/13 it then displays as a dot which is the intended scenario. The cut of seems to be 100 but i can not figure out why?

    Kind regards,


  9. Hi dan not at computer this weekend so can't check but its likely the 'max points in chart' option in the user settings. Anything above this number will show as a line. Thuis applies to all charts / applications

    Nb : this is user specific in the software and is also a separate setting in the server so you will need to check that before publishing.

    Sundanania -will check when I get back to a version of QV!


  10. hey, that did the trick (settings>>user preferences>>objects). THANKS!!!

  11. Hi All, We have been trying to implement a control chart with a dynamic average but so far been unsuccessful. If you have any thoughts on how this could be achieved it would be great if you can take a look at the discussion i started :

    look forward to hearing peoples thoughts.

  12. Thanks for great information you write it very clean. I am very lucky to find your blog & get an useful info tips from you. thanks a lot. QlikView Training in Hyderabad

  13. Fantastic - answers a bunch of questions I have had for a while.
    just one issue - the average I m using is being calculated dynamically too - so how do I get it into the equation above?