I have been searching for an example workbook or instructions that correctly calculates the standard deviation and subsequently the control limits in an X bar chart. All examples I have found use the overall population standard deviation vs the average of the sample standard deviation. Secondly, when calculating a control limit for any given subgroup the average standard deviation must be divided by the square root of the sample size for that subgroup. This is then multiplied by 3 and added or subtracted from the mean to get the UCL and LCL.. These two mistakes will cause the control limits to be significantly further from the mean vs if they were calculated with the correct formulas.
An Xbar chart is a much better tool for sensing a changes vs an individual moving range chart. It has greater sensitivity to change and unlike individual moving range charts it can be used with with non normal data sets. However the correct calculations need to be made.
To calculate the average sample deviation: each subgroup standard deviation is squared. An average of the squares is calculated and then its square root is taken. Taking a simple average of the standard deviations is not the correct method for averaging standard deviations.
Below is the correct formula for the UCL and LCL in a Xbar chart
TermDescriptionμprocess meankTypically 3σaverage standard deviationninumber of observations in subgroup i
Attached is a sample workbook which was prepared in desktop version 10.5 . The control chart is based on [Date_created], with each subgroup identified by [lot_number] and the measured value recorded in [numeric entry]. The data set is filtered to Item A which has 11 lots. Each lot has 10 samples with the resultant value stored in [Numeric Entry]. I have the sum of std_dev squares calculated for each lot. However, I am unable to get the average of the the sum of squares for the filtered item, "A". It will not aggregate over the entire data set.
Once I have that calculated the rest of the calcs are easy.
The below chart was prepared in the statistical software package Minitab V18 and shows the correct Xbar chart for item "A" along with the respective calculated parameters. I have also manual calculated the the UCL in excel for lot A with the method I described above and the numbers match.
Any help is appreciated.
Dave
Dave,
To address your question regarding being able to change the date range, you can try the following:
1. Create parameters for the user to be able to enter the start date and end date for the analysis:
2. Then, creates a calculated field to determine what values to include in the analysis - including only the values that fall between the dates specified in the parameters:
3. Place the Included Value? field on the Filters shelf, and remove the Date Created from the Filter Shelf, and show the parameter controls for the two parameters we created in step 1. So your viz now looks like this:
4. Finally, update all of your calculated measures to include the "Included Value?" field as part of the FIXED phrase. Here is an example of how your would change the avg_std_dev field (and you need to do this for every field):
Now, you can change the date range in the parameters and the values should recalculate as you were expecting. However, to be sure, you should do a manual calc and compare the results, just to be sure it's working as expected.
Hope that helps.
Kaz.
