Probably. Post a sample packaged workbook and I'll play with it a bit. The solution is going to most likely involve LOOKUP(), PREVIOUS_VALUE & SIZE() in some combination. You'll need a field to distinguish what's above and below the centerline.
I've built all of these tests and more into a workbook that can be shared, I presented it at the Tableau Customer Conference today and will be publishing it in the next week once I get it fully cleaned up.
Jonathan, what timing. I can't wait to see what you put together. Please let me know when it's ready.
Shawn, thanks for the response.
Just checkng to see when you might be able to get the Nelson Rules workbook out there. I'll be revisiting it this week.
Rob, I've attached a Word document with the formula I used to build a control chart with these rules. I adapted it from this site: http://dataremixed.com/2011/10/how-to-make-control-charts-with-tableau/ Be careful if you use this site though because the formula he gives is actually incorrect in some places.
While the formula is quite long, I'm sure you can adapt them for your purpose with a general find and replace. Let me give you some idea of what it does. [Data Selection] is equivalent to your [Metric Report Calc] so you can just replace that. [X-Bar Benchmark Average] is equivalent to your [Mean Value]. [X-Bar Benchmark Standard Deviation] is just the standard deviation of a selected date range. It appears you're using moving range to calculate your process limits so this is something you'll have to adjust for your needs.
Keep in mind that I'm also only checking 8 points on one side of the mean, not nine. Also, the formula highlights all points that violate the rules (part of why the formula is so long) so you'll see a great deal of repetition there.
Control Chart Rules Formula.docx 14.7 KB
1 of 1 people found this helpful
Rob - I apologize for the delay, see http://drawingwithnumbers.artisart.org/user-defined-functions-and-process-control-charts/ for a tutorial, workbook, and data source that you can use.
Marc - I also used Ben's post as a launching point, I separated the calcs so I could make them more efficient, have more special cause tests, and generate Run & XbarMR charts. I did as much validation as I could, and I'd be glad to have some more eyes on them for accuracy.
Marc and Jonathan,
Thanks for the replies. Marc, I've been applying and testing the calculations and they are working. I do have a question. The first data point to change color is the last one that meets the test. So even though the previous data points satisfied the test they don't change color. How do I get it to recognize it in color? I've included an examples where it clearly failed the second rule and the third rule. Thoughts?
Thanks in advance,
The formula is actually designed to only highlight the last point that violates the rule, not all points involved. It could be changed to highlight all points involved but doing so would make this formula incredibly long (and it's already way too long!). I think Jonathan's method would probably achieve this though, so you may want to adapt his chart for your purposes.
Jonathan, I started to go weave my way through your workbook several times this week and kept getting distracted. For as far as I got though, it looks great! I can appreciate the amount of time and effort it must have taken to develop this and make sure it works as intended. The one issue I kept running into though is my ability to wrap my head around the PREVIOUS_VALUE() function. For some reason my brain just doesn't want to make sense of it, most likely because it's the end of the day on a Friday. Hopefully I'll get a chance to figure that out next week and finish weaving through the workbook formula by formula. I'll be sure to share my feedback once I've managed to get through it.
Hi Marc, thanks for looking at it! I don't have the easiest time explaining PREVIOUS_VALUE(), I'll practice on you if that's ok...
PREVIOUS_VALUE() - PV from here on out - is Tableau's only self-referencing function. PV returns the value from the prior row in the partition of the measure that PV is part of. The argument that PV takes is used to set the value (and perhaps datatype) of first row in the partition. (I'm assuming you know what a row in the partition is since you posted the control chart doc).
Some examples will help:
- PV(MIN(0)) will return 0 to every row in the partition
- PV(MIN(0))+1 will return 1 for the 1st row in the partition, 2 for the 2nd row in the partition (1 from the value of the 1st row + 1 for the current row), 3 for the 3rd row in the partition, etc. This is like the INDEX() function.
- PV(SUM([Sales]))*1.05 is a compounding interest. The first row in the partition gets the sum of sales * 1.05, the second row gets (sum of sales * 1.05) * 1.05, and so on.
Now, for the interest calc, if we don't actually want the first row to be incremented, then we can do something like
IF FIRST()==0 THEN
In the control chart calcs, I use PREVIOUS_VALUE to create a running counter for trends and shifts. This is much more efficient than the LOOKUPs in Ben's original code because there is only one evaluation for each new mark instead of several lookups. The other piece is that by separating the calculations, we can now report on several special cause signals at once instead of only one of them at a time.
Thanks for the explanation. With this and with some playing around in Tableau, I think I finally have a sense of how PREVIOUS_VALUE works! I can see how using this would be much more effective for counting trends than LOOKUP. I'll set some time aside to finish going through it but so far it look good. I like how each point can be flagged for multiple rule violations, something that is lacking in the way I had done it.
Rob, have you had any luck in implementing either of these strategies?
I used and modified your formulas and just finished my testing. Works great. Currently as you know, it treats the rule calculations as a whole but in the future I would like to divide them out. I will review Jonathan's solution and incorpoate it in the future. Thanks for all the help.
I'm working through your SPC Calculations file and I have a question for you. In step 6 of your process, you reference a calculated field 'Run Chart', but I can't find that field in your SPC Calculations Extract file. I see that it just replicates the chart from the primary data source, I'm just not adept yet on what's going on under the surface. Could you send me the formula (if you still have it?)
Thanks a ton.
The Run Chart formula is [Beginning Variable], which itself points to the measure from the primary source. I did this so that the Run Chart chart would automatically pick up the name "Run Chart" as an axis label, and so that the run chart calcs could refer to the run chart.
Oh, got it! Thanks a ton for your quick reply. I'll let you know how it works out.