Home / Statistical Tools / Control Charts / Levey-Jennings / How-To with GroupBy
How-To with GroupBy¶
This walkthrough charts the same control material on two instruments in one run. GroupBy divides the rows by the value of a grouping column and charts each group separately, so each instrument gets its own mean and its own limits.
On this chart that separation matters more than on most, because the limits are built from the overall standard deviation of whatever rows go into them. Mixing two instruments would produce a single band describing the two of them combined, which is a band that fits neither.
If you have not built a plain Levey-Jennings chart yet, start with the Levey-Jennings How-To and come back.
The data¶
Fifteen consecutive days of the same control material on each of two instruments, in millimoles per litre.
| Instrument | Control Result |
|---|---|
| Instrument A | 4.87 |
| Instrument A | 4.98 |
| Instrument A | 4.76 |
| Instrument A | 5.14 |
| Instrument A | 4.78 |
| Instrument A | 4.96 |
| Instrument A | 4.68 |
| Instrument A | 4.91 |
| Instrument A | 5.00 |
| Instrument A | 5.32 |
| Instrument A | 5.03 |
| Instrument A | 5.24 |
| Instrument A | 5.16 |
| Instrument A | 5.05 |
| Instrument A | 5.19 |
| Instrument B | 5.01 |
| Instrument B | 4.94 |
| Instrument B | 5.14 |
| Instrument B | 4.86 |
| Instrument B | 4.92 |
| Instrument B | 4.80 |
| Instrument B | 4.90 |
| Instrument B | 4.79 |
| Instrument B | 4.86 |
| Instrument B | 4.96 |
| Instrument B | 6.37 |
| Instrument B | 4.68 |
| Instrument B | 4.84 |
| Instrument B | 4.90 |
| Instrument B | 4.90 |
The Instrument column is the grouping column. It is not a measurement, and you do not check it in the measurement list.
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 thirty results in rows 2 through 31.
-
Start the chart
From the Excel ribbon: QXL Stat Tools Tab > Control Charts > Variables > Charts as Individuals > Levey-Jennings.
-
Give it the cells
In the Data Selection window set Selected Range: to A1:B31. Leave Data in Columns selected and First Row/Column is Header checked. Press Next >.
-
Check the measurement column only
On the Data tab, check Control Result. Leave Instrument unchecked: it is about to become the grouping column, and a column can only do one job at a time.
-
Switch the calculation type to GroupBy
Change the calculation type from Excel to GroupBy. The GroupBy region appears below it.
-
Move Instrument into the GroupBy order
Select Instrument 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. They are named Levey-Jennings - Instrument A and Levey-Jennings - Instrument B, each with one panel of fifteen points.
| Worksheet | Center line (mean) | Lower control limit | Upper control limit | Sigma (overall) |
|---|---|---|---|---|
| Levey-Jennings - Instrument A | 5.00 | 4.45 | 5.56 | 0.19 |
| Levey-Jennings - Instrument B | 4.99 | 3.81 | 6.18 | 0.40 |
Instrument A is clean. None of the six Westgard tests marks any of its points.
Instrument B has one point out of control. Its eleventh result plots at 6.37, above that instrument's upper control limit of 6.18, and is drawn in a different color.
Look at how different the two bands are. The two means are within 0.02 of each other, and yet Instrument B's band is more than twice as wide: 2.4 units from limit to limit against Instrument A's 1.1. Nothing about Instrument B is noisier than Instrument A except that single bad day, and because the sigma on this chart is the overall standard deviation, that one value more than doubled it.
That is the practical lesson of this page. GroupBy gave each instrument its own limits, which is right. It also means one upset on one instrument widens only that instrument's band, and widens it a lot. Instrument B's result of 6.37 is barely outside its own limits, while it would have been far outside limits built from Instrument A's data.
Things to try next¶
- Run it without GroupBy. Set the calculation type back to Excel and finish again. You get one chart of thirty points with one band describing both instruments, and no indication on the chart of which instrument any point came from.
- Exclude the bad point rather than grouping around it. Mark the 6.37 result as an outlier and run again. Instrument B's limits tighten sharply, and the excluded point is still plotted so you can see where it fell.
- Use historical values instead. Supply the mean and sigma from a validated lot in the Historical Values group and both instruments are judged against the same external band, which is a different and often more useful question than whether each instrument is stable against itself.