Advanced six sigma process control chart approach to setting an alert

[Richard_Byard]

An advanced use of alerts which can notify you when something out of the ordinary has occurred can be setup applying the concept of a process control chart. For further information on process control charts see https://www.isixsigma.com/tools-templates/control-charts/a-guide-to-control-charts/

The concept of the process control chart is to monitor what is a normally a consistent measure (something that doesn't grow over time such as revenue). In this example I have used Gross Margin % which would logically be expected to sit within a certain, reasonably tight, range of values. The chart below shows a process control chart where the upper and lower bounds are set at 2 standard deviations from the mean. In a normally distributed dataset this would mean that 95% of the values would sit within these upper and lower bounds, if this was increased to 3 standard deviations would increase this to 99.7%.

The idea of this example is to set the alert so that every new datapoint added into this chart would be analysed based on dynamic calculation of the upper and lower bounds, and an alert triggered if we are outside of these bounds.

Check out this great video from Michael Fawcett on the Qlik help channel https://youtu.be/qSEOyXpVDxk for another example and/or follow the steps below.

When to use this approach

• When you have a timeseries set of data in your application
• When you have a measure that is normally reasonably consistent and you want to be notified when something unusual has occured.

Setup your alert

The first thing to identify before you setup your alert is how you will identify the latest date with actuals, this can be problematic when you have budgets and forecasts so your calendar extends into the future. To do this you will either have the developer of the data model add a flag into the dataset that can be brought in as an additional measure in the alert e.g. TodayFlag = 1, or you can add it as a custom measure into the alert as we will in this example e.g. if(OrderDate=max(TOTAL OrderDate),1,0).

To create your alert, do the following:

1. Create a new alert in the Qlik Alerting web portal
2. In the Data section
   1. Give your alert a name
   2. Pick the measure (e.g. Gross Margin %) you wish to monitor
   3. Add a second measure which can be the flag which identifies the latest date, e.g. TodayFlag, or enter the expression as a custom measure to flag the latest actual date, e.g. if(OrderDate=max(TOTAL OrderDate),1,0) and call this measure TodayFlag.
   4. Select the date/timestamp dimension to provide the timeseries dataset for the measure, e.g. OrderDate
3. In the Conditions section
   1. Enter a first condition (A) to set the upper boundary limit
      1. Column = Gross Margin %
      2. Operator = greater than (>)
      3. Type = Set
      4. Aggregation = Standard Deviation
      5. Standard Deviation = 2
   2. Enter a second condition (B) to set the lower boundary limit
      1. Column = Gross Margin %
      2. Operator = less than (<)
      3. Type = Set
      4. Aggregation = Standard Deviation
      5. Standard Deviation = -2
   3. Enter a third condition (C) to pick the latest date/timestamp 
      1. Column = TodayFlag
      2. Operation = equals (=)
      3. Type = Manual
      4. Value = 1
4. The important part to make this alert work is to set the rules for the conditions in the correct order as in this case we need to evaluate the full data set for conditions A and B before applying condition C as an additional filter.
   1. In the first rule enter A or B
   2. click on the create new rule button, In this second rule enter C 
5. Complete the setup of your alerts for the Schedule, Distribution and Notification sections as per normal.
   Note: You may want to change the notification body to make the alert message a little clearer.

An example trigger

On the next reload we get an anomaly when the Gross Margin % jumps to over 50% due to a cost allocation issue in our underlying source systems. This alert allows us to know immediately that something is incorrect so we can review the data lineage and address the issue straight away improving confidence in the data and ensuring that no erroneous decisions are made.