Home / Statistical Tools / Analysis Tools / Capability Scorecard / How-To / GroupBy
GroupBy¶
Use GroupBy to produce a separate scorecard for each group in your data, so two plants, lines or shifts each get their own table and their own pair of Pareto charts instead of sharing one.
Goal¶
Compare the same four process steps at two plants, and see which step each plant should look at first.
Sample Data¶
The same four steps were inspected at both plants, 500 units at each step. Press Copy for Excel, then paste into a blank worksheet.
| Plant | Process Step | Units | Defects | Opportunities |
|---|---|---|---|---|
| Plant A | Cutting | 500 | 10 | 3 |
| Plant A | Drilling | 500 | 6 | 4 |
| Plant A | Deburring | 500 | 20 | 2 |
| Plant A | Assembly | 500 | 4 | 5 |
| Plant B | Cutting | 500 | 25 | 3 |
| Plant B | Drilling | 500 | 9 | 4 |
| Plant B | Deburring | 500 | 18 | 2 |
| Plant B | Assembly | 500 | 8 | 5 |
Plant is the grouping column. It is not a Units, Defects, Opportunities or Name column, and it does not go in any of those four slots.
Steps¶
-
Put the data in Excel
Press Copy for Excel above the table, click cell A1 in a blank worksheet, and press Ctrl+V. Headers land in row 1 and the eight rows in rows 2 through 9.
-
Launch the analysis
From the Excel ribbon, select QXL Stat Tools → Analysis Tools → Capability Scorecard.
-
Select your data
Select cells A1:E9, the header row plus all eight data rows across all five columns.
-
Configure the analysis
In the Capability Scorecard dialog:
- Select the GroupBy radio button instead of Excel
- Number of Units: select Units
- Number of Defects: select Defects
- Number of Opportunities: select Opportunities
- Name: select Process Step
- GroupBy: move Plant into the GroupBy Order list
Click Finish.
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.
Result¶
Two scorecards, one per plant, each with its own four-step table, its own totals row and its own two Pareto charts.
Plant A
| Process Step | DPU | DPO | DPMO |
|---|---|---|---|
| Cutting | 0.020 | 0.006667 | 6,667 |
| Drilling | 0.012 | 0.003000 | 3,000 |
| Deburring | 0.040 | 0.020000 | 20,000 |
| Assembly | 0.008 | 0.001600 | 1,600 |
| Total | 0.020 | 0.005714 | 5,714 |
Plant B
| Process Step | DPU | DPO | DPMO |
|---|---|---|---|
| Cutting | 0.050 | 0.016667 | 16,667 |
| Drilling | 0.018 | 0.004500 | 4,500 |
| Deburring | 0.036 | 0.018000 | 18,000 |
| Assembly | 0.016 | 0.003200 | 3,200 |
| Total | 0.030 | 0.008571 | 8,571 |
Plant B has half again as many defects per unit overall, 0.030 against 0.020, from the same 2,000 units inspected at each plant.
The two plants have different worst steps. Deburring is the top of Plant A's DPU Pareto at 0.040. At Plant B, Cutting is the top at 0.050 and Deburring is second at 0.036.
And at Plant B the two Pareto charts disagree with each other. Sorted by DPU the order is Cutting, Deburring, Drilling, Assembly. Sorted by DPMO it is Deburring, Cutting, Drilling, Assembly, because Cutting is counted against three opportunities per unit and Deburring against only two: 25 defects over 1,500 opportunities is a lower rate than 18 defects over 1,000. Plant A's two charts happen to agree, so a reader who only ever saw Plant A might not know the two orderings can differ at all.
How GroupBy works here
Quantum XL creates a separate scorecard for every distinct value in the grouping column, and each one is built only from its own rows, including its totals row. Without GroupBy you get a single eight-row table in which Cutting, Drilling, Deburring and Assembly each appear twice, with nothing on the table or on either Pareto chart saying which plant a row came from, and one totals row covering both plants.