Home / Statistical Tools / Control Charts / G Chart / How-To with GroupBy
How-To with GroupBy¶
This walkthrough charts infections on two wards in one run. GroupBy divides the rows by the value of a grouping column and charts each group separately, so each ward gets its own worksheet, its own estimated event rate and its own limits.
That last part is what matters on this chart. A G chart's entire band comes from one number, the event probability, so a ward with a genuinely different rate needs its own estimate or the chart describes neither ward. Both wards below are in control on their own charts. Pooled into one chart, a gap that is perfectly ordinary for one of them is marked.
If you have not built a plain G chart yet, start with the G Chart How-To and come back.
The data¶
Fourteen infections from each of two wards, each value the number of patients discharged since that ward's previous infection.
| Ward | Patients Between |
|---|---|
| Ward A | 3 |
| Ward A | 47 |
| Ward A | 36 |
| Ward A | 7 |
| Ward A | 17 |
| Ward A | 15 |
| Ward A | 26 |
| Ward A | 39 |
| Ward A | 2 |
| Ward A | 0 |
| Ward A | 126 |
| Ward A | 14 |
| Ward A | 36 |
| Ward A | 0 |
| Ward B | 3 |
| Ward B | 7 |
| Ward B | 1 |
| Ward B | 15 |
| Ward B | 12 |
| Ward B | 0 |
| Ward B | 0 |
| Ward B | 4 |
| Ward B | 15 |
| Ward B | 2 |
| Ward B | 1 |
| Ward B | 3 |
| Ward B | 0 |
| Ward B | 1 |
Ward is the grouping column. It is not a count and you do not check it in Data Columns (required).
The two wards are visibly different before any chart is drawn: Ward A's gaps run into the tens, Ward B's are mostly single digits. Ward B has more infections per patient discharged.
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-eight counts in rows 2 through 29.
-
Start the chart
From the Excel ribbon: QXL Stat Tools Tab > Control Charts > Rare Event Charts > G Chart (Count Between Events).
-
Give it the cells
In the Data Selection window set Selected Range: to A1:B29. Leave Data in Columns selected and First Row/Column is Header checked. Press Next >.
-
Check the count column only
On the Data tab, check Patients Between in Data Columns (required). Leave Ward unchecked: a column can only do one job at a time, and Ward is about to become the grouping column. If Patients Between did not come in as a Count, set it with the Data Types button.
-
Switch the calculation type to GroupBy
Change the calculation type from Excel to GroupBy. The GroupBy region appears below it.
-
Move Ward into the GroupBy order
Select Ward 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 G Chart - Ward A and G Chart - Ward B, each with one panel of fourteen points.
| Ward A | Ward B | |
|---|---|---|
| pHat | 0.0340 | 0.1667 |
| One infection per how many patients | about 29 | about 6 |
| CenterLine | 19.02 | 2.82 |
| UCL | 189.84 | 35.26 |
| LCL | 0 | 0 |
Both wards are in control. Not one point is marked on either chart.
The two charts are barely comparable, and that is the finding. Ward B's estimated event rate is 4.9 times Ward A's: about one infection per 6 patients discharged against one per 29. Everything else follows from that single number. Ward A's center line is nearly seven times Ward B's, and its upper limit is more than five times as far out. Neither chart is wrong; they describe two wards with genuinely different rates, and each was estimated only from its own fourteen events.
Ward A's longest gap is ordinary for Ward A. It is 126 patients, against Ward A's own upper limit of 189.84, comfortably inside. On a ward that averages one infection per 29 discharges, a gap of 126 is a good stretch but not a signal.
Things to try next¶
-
Run it without GroupBy. Set the calculation type back to Excel and finish again. One worksheet, twenty-eight points, one rate for both wards:
Value pHat 0.0587 One infection per how many patients about 17 CenterLine 10.47 UCL 108.24 The pooled rate lands between the two wards, as it must, and now Ward A's gap of 126 is marked out of control, 17.8 counts above the pooled upper limit of 108.24. Nothing about either ward changed between the two runs of the analysis. The gap is a signal against a rate that is the average of two wards and the true rate of neither, and it is the only marked point on the pooled chart. Ward B, meanwhile, is judged against limits far wider than its own, so a real deterioration there would have to be extreme before the pooled chart noticed.
-
Compare the two pHat values directly. They are on each worksheet's summary table. On this chart that one number is the whole model: quote it, and everything else on the chart can be derived from it.
- Set a common rate deliberately. If both wards are supposed to run at the same rate, type it into Historical pHat (optional): and run each ward against that shared standard instead of against its own history. That is a different question from the one GroupBy answers on its own, and the two settings combine.
- Group by two columns. Add a second grouping column and it becomes
2.in GroupBy Order, subdividing what1.produced. Each combination needs at least three events of its own, so subdividing a rare-event record runs out of data quickly.