How to Visualize System Data as a Process Behaviour Chart

Last week I wrote a short post about presenting COVID-19 case and testing data visually using a special graph called a process behaviour chart (PBC) and how it improves our understanding by giving us simple, built-in aids to separate exceptional "signals" from routine "noise". In this post, I'm going to walk through how you can create your own.

For the purposes of this tutorial, I'm going to assume you have access to Excel or a similar spreadsheet tool, which will make this significantly easier than doing it by hand, although you could do so with a calculator and graph paper.

Start With Time-Sequenced Data

PBCs are designed to work with data that is collected about how a process works over time, so it shouldn't be surprising that we want to start with gathering representative figures from the systems we're interested in understanding. I'm going to look at the past twenty-six days of new COVID-19 cases reported in Ontario which I've gathered from the Ministry of Health website each and every day. I'm going to put this under its own column, and add some additional headings for the calculations I'll be doing on the data:

Under the first cell of the AVG header I'm going to, you guessed it, calculate the AVERAGE of the case data. I'm then going to create a reference chain in the cell below it and drag it down the entire list.

This will create the data points for the Central Line in the chart:

Next, we'll employ a technique developed by Dr. Donald Wheeler that will help lay the foundation for determining the Upper and Lower Process Limits by calculating the Moving Range (mR) of the data set. We do this using Excel's ABS function to determine the raw difference between the first two cells:

As with the average cell data, we'll drag the handle on the resulting cell to replicate the calculation to the bottom of the data set.

Next, we calculate the average of the moving range the same way as with the AVG column:

We now have everything we need to calculate the Upper and Lower Process Limits: The basic formula we will use is:

Upper Process Limit: AVG + 3 * mR-BAR / 1.128

Lower Process Limit: AVG - 3 * mR-BAR / 1.128

Why 1.128? This is a statistical constant that allows us to "scale" an estimate of three standard deviations from the average of the moving range of the daily new cases.

As with the mR column, we'll drag the formulas down from the first cells to the bottom of the data set range. I'll also take the opportunity to adjust the precision of the AVG, mR-BAR, UPL, and LPL columns to two decimal places to make it easier to read.

It's now a straight-forward process to create a line chart from this data: We'll want to show the New Over 24h, AVG, UPL, and LPL columns. It will look something like this:

In order to make this a "proper PBC", I make the following changes to the data series:

• Thicken the UPL and LPL to 3pt and color red.
• Thicken the AVG (Central Line) to 3pt and color green.
• Add markers to the Data Points and size to 7pt.
• Delete the horizontal lines
• Adjust the Y-axis scale to fit the data better.
• Add data labels to the Central Line and Upper and Lower Process Limits to aid in understanding the scale of variation

Depending on your Excel-fu, you should arrive at something like the following:

Apply the Rules

Once you've got your data presented into a PBC, you can apply the three rules I described in my last post to identify instances or patterns of exceptional variation in the data. I can see three:

• Data Point 6 is below the Lower Process Limit
• Data Points 14-23 are all above the Central Line
• Data Point 24 is above the Upper Process Limit

I now have a starting point to ask interesting questions about how the attendant systems were working on three occasions. Note this doesn't mean something wrong has happened, but that some event or other factor influenced a change in the data away from where we'd have expected. This is informed by understanding the theory of how systems work and the variation they exhibit over time.

NB: I've left out how to shift the limits in response to exceptional variation indicators in the PBC as this is a bit more advanced and can easily frustrate those new to making these charts. I will cover this and how to make the corresponding Moving Ranges (mR) chart in a subsequent post.

Conclusion

You now have a rough idea on how to construct your own process behaviour charts from raw, time-sequenced data. This technique can be broadly applied to data from a wide variety of sources to aid in better understanding when a system is exhibiting routine or exceptional variation, and to begin asking more interesting questions instead of reacting to the last datapoint. The adventurous may even find the exercise worthy to run through with their colleagues and managers - it might provoke some interesting conversations!

Acknowledgements

I learned this technique from author and consultant Mark Graban after having him deliver a workshop here in Toronto in March of 2019, and the books and writings of Dr. Donald J. Wheeler. I highly recommend picking up their respective books for further study.

Understanding Variation, Donald J. Wheeler, SPC Press, 1993.

Twenty Things You Need to Know, Donald J. Wheeler, SPC Press, 2009.

Measures of Success, Mark Graban, Constancy, Inc., 2018.