Home / Statistical Tools / Control Charts / EWMA / How-To with GroupBy
How-To with GroupBy¶
This walkthrough charts the same measurement from two plating baths in one run. GroupBy divides the rows by the value of a grouping column and charts each group separately, so each bath gets its own center line, its own sigma estimate, and its own widening band.
On an EWMA chart that last part is what you can see at a glance. The band is built from the group's own within-subgroup standard deviation, so a noisier bath gets a wider funnel and a steadier bath a narrower one. The two charts here come out the same shape and different sizes, and the smaller of the two changes is the one that gets marked.
If you have not built a plain EWMA chart yet, start with the EWMA How-To and come back.
The data¶
Twenty-four consecutive hours from each of two plating baths, one concentration reading per hour, in grams per litre. Bath B runs at a lower level than Bath A and holds it more tightly. From its reading 18 onward Bath B runs higher and stays there.
| Bath | Concentration |
|---|---|
| Bath A | 100.05 |
| Bath A | 100.56 |
| Bath A | 99.45 |
| Bath A | 100.42 |
| Bath A | 101.11 |
| Bath A | 100.49 |
| Bath A | 101.87 |
| Bath A | 98.94 |
| Bath A | 100.08 |
| Bath A | 99.15 |
| Bath A | 99.06 |
| Bath A | 99.78 |
| Bath A | 100.27 |
| Bath A | 100.50 |
| Bath A | 100.61 |
| Bath A | 102.68 |
| Bath A | 101.04 |
| Bath A | 98.09 |
| Bath A | 100.25 |
| Bath A | 99.25 |
| Bath A | 99.38 |
| Bath A | 101.57 |
| Bath A | 99.73 |
| Bath A | 97.66 |
| Bath B | 96.97 |
| Bath B | 95.66 |
| Bath B | 96.61 |
| Bath B | 96.12 |
| Bath B | 96.07 |
| Bath B | 96.01 |
| Bath B | 96.09 |
| Bath B | 96.48 |
| Bath B | 96.22 |
| Bath B | 96.80 |
| Bath B | 95.87 |
| Bath B | 96.42 |
| Bath B | 96.14 |
| Bath B | 96.76 |
| Bath B | 95.91 |
| Bath B | 96.07 |
| Bath B | 96.07 |
| Bath B | 96.98 |
| Bath B | 96.57 |
| Bath B | 96.85 |
| Bath B | 97.08 |
| Bath B | 97.70 |
| Bath B | 98.23 |
| Bath B | 97.32 |
The Bath column is the grouping column. It is not a measurement, and you do not check it in the measurement list.
The rise in Bath B is 1.10 grams per litre. Look for it in the column and it is hard to find. Eleven of Bath A's twenty-three hour-to-hour moves are larger than that on their own, the biggest being 2.95. Keep both numbers in mind for the last section.
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 forty-eight readings in rows 2 through 49.
-
Start the chart
From the Excel ribbon: QXL Stat Tools Tab > Control Charts > Time Weighted Charts > EWMA.
-
Give it the cells
In the Data Selection window set Selected Range: to A1:B49. Leave Data in Columns selected and First Row/Column is Header checked. Press Next >.
-
Check the measurement column and leave the subgroup size at 1
The EWMA Chart dialog opens on the Data tab. Check Concentration. Leave Bath unchecked: a column can only do one job at a time, and Bath is about to become the grouping column.
Leave the Subgroups group on Constant size: with
n = 1, which is one reading per point. Withn = 1the estimators offered on the next tab change to the individuals set, with Average moving range selected. Leave the default. -
Switch the calculation type to GroupBy
Change the calculation type from Excel to GroupBy. The GroupBy region appears below it.
-
Move Bath into the GroupBy order
Select Bath 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.
-
Leave the EWMA weight at its default
On the Limits & Estimation tab the EWMA Weight group holds EWMA weight (λ):, default 0.2. Leave it. One weight applies to every group, so whatever you set here is used on both charts.
-
Finish
Press Finish.
What you should see¶
Two worksheets, not one. They are named EWMA - Bath A and EWMA - Bath B, each holding one panel of twenty-four points with a center line and a band that widens from left to right.
Every number below belongs to one bath alone. Nothing on either sheet was computed from the other bath's rows.
| Worksheet | Center line | Average moving range | Sigma (within) | Lower limit at point 24 | Upper limit at point 24 |
|---|---|---|---|---|---|
| EWMA - Bath A | 100.08 | 1.22 | 1.08 | 99.01 | 101.16 |
| EWMA - Bath B | 96.54 | 0.50 | 0.44 | 96.10 | 96.98 |
Those two limits are the ones at the last plotted point, which is where the band is at its widest. They are the pair the summary table reports. Earlier points were judged against narrower limits.
The two funnels are the same shape at different sizes. Read the half width down each column and then compare it against that bath's sigma:
| Plotted point | Bath A half width | Bath B half width | Half width as a multiple of that bath's sigma |
|---|---|---|---|
| 1 | 0.65 | 0.26 | 0.60 |
| 2 | 0.83 | 0.34 | 0.77 |
| 3 | 0.93 | 0.38 | 0.86 |
| 5 | 1.02 | 0.42 | 0.94 |
| 10 | 1.07 | 0.44 | 0.99 |
| 24 | 1.08 | 0.44 | 1.00 |
The right-hand column is identical for both baths because the funnel's shape depends only on the weight and on how far along the series a point sits, not on the data. What differs is the scale it is drawn at. Bath A's band is 2.4 times as wide as Bath B's, and that ratio is nothing more than the ratio of their two sigma estimates, 1.08 against 0.44.
Bath A is clean. No point on it is marked.
Bath B has two points out of control, 23 and 24. They plot at 97.11 and 97.15 against an upper limit of 96.98. The chart reports late, as an EWMA chart does: the level moved at reading 18 and the chart says so at reading 23.
The smaller change is the one that signals
Bath B's rise of 1.10 is 2.5 times its own sigma, and it clears Bath B's band. The same 1.10 rise in Bath A would be only 1.0 times that bath's sigma. Add 1.10 to Bath A's last seven readings and run it again: not one point is marked, and its highest plotted point stays a comfortable 0.45 sigma inside its own limit.
So the same change in grams per litre signals in one bath and not in the other. That is the whole point of grouping. Each band is built from the variation of the bath it is drawn on, and a change is judged against the noise it has to be seen through.
Only one test runs on this chart
EWMA offers Rule 1 and nothing else, on each group's chart independently. See Out-of-Control tests.
Things to try next¶
- Run it without GroupBy. Set the calculation type back to Excel and finish again. You get one chart of forty-eight points with a single center line at 98.31, which sits above every Bath B reading and below most of Bath A's, and a single sigma of 0.76, which describes neither bath. Forty-four of the forty-eight points are marked out of control. The chart is not reporting a process problem, it is reporting that it was given two processes and told to treat them as one.
- Change the weight and watch both charts change together. Set EWMA weight (λ): to 0.4 and run again. Both funnels settle at a larger final width and both plotted lines follow their data more closely. There is one weight box, not one per group.
- Estimate the limits from the settled stretch. Set Estimate Limits From to the first 17 points. Each bath's center line and sigma are then built from its own first 17 readings, and the count applies to each group separately rather than to the first 17 rows of the sheet. Bath B's center line drops from 96.54 to 96.25, computed from before its change, and its later points sit further outside. Bath A changes too, even though it had no shift to exclude: its sigma falls from 1.08 to 0.87, because the largest hour-to-hour moves in that bath happen to fall in its last seven readings. Restricting the baseline is not a free operation on a group that looks stable.
- Group by two columns. Add a second grouping column and it becomes
2.in GroupBy Order, subdividing what1.produced. - Compare with a split instead. Split Control Limits also gives each phase its own center line, its own limits, and its own restarted funnel, but it divides one chart by position rather than by the value of a column. Use a split when a single stream changed over time, GroupBy when the rows describe separate things.