September 16, 2015

Tableau Control Charts - Resetting Control Limits and Signals for Process Changes

1st off...Kudos to Ben Jones of Tableau for his work and his willingness to share his own knowledge on how to build process control charts using Tableau Desktop. This is something my 6 sigma leaders wanted badly and it's not an out-of-the-box option with Tableau...yet.

Here's the links to a few of the blogs Ben has written on the topic:

http://dataremixed.com/2011/09/tom-brady-and-control-charts-with-tableau

http://dataremixed.com/2011/10/how-to-make-control-charts-with-tableau

https://public.tableau.com/s/blog/2013/how-to-make-control-charts-tableau

(ALSO CHECK OUT BEN'S BOOK: Communicating Data With Tableau


Notice that there is an outlier on the right portion of this viz that falls within the LCL. This raised a red flag (or dot...haha). After a bunch of comparing some rather lengthy calculations, we came to the conclusion that the table calculations needed to be "computed using Pane Across" rather than the default "Window Across:"


This step will ensure that your LCL, UCL & Signals calculations will reset at each pane as shown below for this example...no more red alert:


In all honesty, I never could get Ben's dashboard to break. As I look closer, Ben was most likely able to accomplish the same by using "Compute at Date". Not sure which will work for you, but I wanted to be put this out there in case you run into something similar.

Cheers!

Kevin Taylor





13 comments:

  1. Hi Kevin,

    is there a link to the Tableau Workbook you used? I am strongly interested in the way you recalculated the control limits within Tableau. I have not found yet any example on how this is triggered dynamically in Tableau based on a series of points below/above the central line. Any help is appreciated, Thanks!

    ReplyDelete
  2. Hello! Unfortunately I can't post the workbook as it is today as it contains confidential data. However...Ben Jones has some published workbooks in his blogs which I have provided links to. If those don't work, let me know and I will see if I can mask the data to share.
    Thanks for reading!

    ReplyDelete
  3. Ben's workbooks unfortunately do no have a feature that triggers a dynamic recalculation of the central line and upper/lower control limits. According to Donald Wheeler's approach, this recalculation should be done after a long run of usually 8 datapoint below or above the central line. This means that you cannot set a fixed length fpr the recalculation intervals (as senn in some example workbooks), but that the lengths depends on your actual data.

    ReplyDelete
  4. I also wonder if you could provide some more information, preferably your formula, for the reference line recalculation. I have been looking everywhere for this.
    Thank you

    ReplyDelete
  5. Please download the workbook in Tableau Public Director, Ben Jones' post: https://public.tableau.com/s/blog/2013/11/how-make-control-charts-tableau

    In this workbook, the reference lines are leveraging the UCL and LCL calculated fields. to reset these lines for a new process control, add your dimension to the columns ("Group By" field) and then edit your reference line scope to "per pane". do the same for your signals (in Ben's example he "computes using date"...my method is to "compute using pane across"...2 methods same result.

    Hope this helps.

    ReplyDelete
  6. Please download the workbook in Tableau Public Director, Ben Jones' post: https://public.tableau.com/s/blog/2013/11/how-make-control-charts-tableau

    In this workbook, the reference lines are leveraging the UCL and LCL calculated fields. to reset these lines for a new process control, add your dimension to the columns ("Group By" field) and then edit your reference line scope to "per pane". do the same for your signals (in Ben's example he "computes using date"...my method is to "compute using pane across"...2 methods same result.

    Hope this helps.

    ReplyDelete
  7. Thanks, Kevin. Still the recalculation of the central line is triggered by a dimension (in case of Ben's workbook "Group By"). I am looking for a way to trigger a recalculation (or a dimensional grouping) dynamically, based on a short or long run of the values. Is this even possible inside of Tableau or do I have to use R?

    ReplyDelete
  8. Thanks for this advice. I've tried to reverse engineer Ben's Peyton Manning workbook (the download is a .twbx file, so a lot of what's under the hood isn't apparent). One key for me was putting the "Group By" dimension up into the columns shelf. One problem, though: Since "DEN" comes before "IND" alphabetically, it puts all the data for Denver first. Do you know how Ben managed to get IND to show up first?

    ReplyDelete
    Replies
    1. you should be able to right click the field, click sort and choose the manual sort option, then just drag IND to the top...or just sort that field in descending order...although that won't work in all instandces

      Delete
  9. Thanks for the response, Kevin! That helps.

    ReplyDelete
  10. I'm still having a problem. I believe I have built my chart as Ben did, and I thought that I had this segmenting or staging problem licked, but then I realized that everything is not being calculated by stage. For some reason, in mine, the average moving range is only being calculated at the window level. As a result, the limits are all incorrect once I invoke the stages...they are all based on an average of all the moving ranges, not the ones in each segment. It's odd, too, because the actual average reference lines do recalulate per stage. I've tried changing the calculation to by pane, but that puts all the data points into each pane. The signals end up messed up, too, because they stay on even after the segments would turn them off. Any idea what I'm doing wrong?

    ReplyDelete
  11. Let me amend that last comment. When I use pane(across) it does not put all the data points into each stage. I have a calculated field that allows me to set up four stages based on dates. When I turn a stage on, the average lines for both charts move up to the average per stage. The UCLmR is constant all the way across, because it's using all the moving ranges in the record. The UCLx and LCLx are at the same distance from X-bar for each segment (when they should be wider when the mR-bar for that segment is larger). When I turn on pane(across), what happens is that the entire timeframe is shown in each segment or pane, with just the data from that segment showing up. So, for example, I have data from November 2014 to June 2017 in this chart. Without any segmenting, I have one pane, one window, with an average moving range of 190.4. If I set up a segment from November 2014 to November 2015, I will have two segments or panes; one with the data from November 2014 to October 2015, and the other from November 2015 to June 2017. The average lines are different in each pane. The average moving range, though, is still 190.4 all the way across. If I then change to pane(across), I get two segments, but now they are each the same length...they both start at November 2014 and end at June 2017. In the first pane, the data for November 2014-October 2015 are there (followed by nothing but blank space). In the second pane, there is blank space from November 2014 to October 2015, and then the data points from November 2015 - June 2017. If there was a way for me to post my file, I would, so you could see it.

    ReplyDelete