Home / Statistical Tools / Control Charts / p Chart / How-To with GroupBy
How-To with GroupBy¶
This walkthrough charts order errors at two distribution sites in one run. GroupBy divides the rows by the value of a grouping column and charts each group separately, so each site gets its own worksheet, its own pooled proportion and its own limits.
On a p chart that separation matters because one number, \(\bar{p}\), sets the whole band. Both sites below are in control on their own charts. Pooled into one chart, three of Site B's ordinary days are marked out of control.
If you have not built a plain p chart yet, start with the p Chart How-To and come back.
The data¶
Ten days from each of two sites. Both sites checked the same numbers of orders on the matching days, so the only thing that differs is the error counts.
| Site | Errors | Orders |
|---|---|---|
| Site A | 5 | 200 |
| Site A | 3 | 220 |
| Site A | 0 | 190 |
| Site A | 5 | 210 |
| Site A | 2 | 205 |
| Site A | 2 | 195 |
| Site A | 8 | 215 |
| Site A | 4 | 200 |
| Site A | 4 | 190 |
| Site A | 7 | 210 |
| Site B | 8 | 200 |
| Site B | 11 | 220 |
| Site B | 6 | 190 |
| Site B | 10 | 210 |
| Site B | 18 | 205 |
| Site B | 17 | 195 |
| Site B | 7 | 215 |
| Site B | 8 | 200 |
| Site B | 17 | 190 |
| Site B | 9 | 210 |
Site is the grouping column. It is neither a defectives column nor a sample size, and you do not check it in either slot.
Steps¶
-
Put the data in Excel
Press Copy for Excel above the table. In Excel, open a blank worksheet, click cell A1, and press Ctrl+V. Headers land in row 1 and the twenty days in rows 2 through 21.
-
Start the chart
From the Excel ribbon: QXL Stat Tools Tab > Control Charts > Attribute Charts > P, NP, and Laney's P'.
-
Give it the cells
In the Data Selection window set Selected Range: to A1:C21. Leave Data in Columns selected and First Row/Column is Header checked. Press Next >.
-
Fill the two data slots
On the Data tab, with Variant: on P, check Errors in Defectives Column, then set Sample Size to Column and choose Orders. Leave Site out of both.
-
Switch the calculation type to GroupBy
Change the calculation type from Excel to GroupBy. The GroupBy region appears below it.
-
Move Site into the GroupBy order
Select Site in Available Items and press
>. It moves into GroupBy Order as1.Do not switch back to Excel after this
Switching the calculation type back to Excel clears the GroupBy list without warning, and returning to GroupBy means building it again.
-
Finish
Press Finish.
What you should see¶
Two worksheets, not one, named P Chart - Site A and P Chart - Site B, each with one panel of ten points.
| Site A | Site B | |
|---|---|---|
| p-bar | 0.0197 | 0.0545 |
| One error per how many orders | about 51 | about 18 |
| UCL at 200 orders | 0.0491 | 0.1027 |
| LCL at 200 orders | 0 | 0.0064 |
Both sites are in control. Not one point is marked on either chart.
Site B makes about 2.8 times as many errors per order as Site A, and that single difference reshapes its whole chart. Its center line is nearly three times as high, its band is about twice as wide, and its lower limit lifts off zero: at Site A three standard deviations below the center line is still negative and gets clamped, while at Site B it lands at 0.0064. A p chart only gains a usable lower limit once the rate and the sample size are large enough, and here one site has one and the other does not.
Things to try next¶
-
Run it without GroupBy. Set the calculation type back to Excel and finish again. One worksheet, twenty points, one pooled proportion for both sites:
Value p-bar 0.0371 UCL at 200 orders 0.0772 LCL 0 The pooled proportion lands between the two sites, and now three points are marked: points 15, 16 and 19, which are Site B's days 5, 6 and 9. Nothing about either site changed between the two runs. Those three days are ordinary for Site B, all comfortably inside Site B's own upper limit of about 0.10, and they are only signals against a rate that is the average of two sites and the true rate of neither. Site A, meanwhile, is judged against a band nearly twice as wide as its own, so a real deterioration there would have to be large before the pooled chart noticed.
-
Compare the two sigma_Z values. Each worksheet reports one. They are 1.22 for Site A and 1.49 for Site B here, both close enough to 1 that the binomial model is a reasonable description of each site on its own. It is worth checking those before concluding that a difference between sites is the only thing going on.
- Compare with a split instead. Split Control Limits also gives separate stretches their own estimate, but it divides one chart by position rather than by the value of a column. Use a split when one site changed over time, GroupBy when the rows describe different sites.
- Group by two columns. Add a second grouping column and it becomes
2.in GroupBy Order, subdividing what1.produced. Each combination needs at least two subgroups of its own.