Home / Statistical Tools / Analysis Tools / Time Series / How-To / GroupBy
GroupBy¶
Use GroupBy to run a Time Series analysis separately on each region, line or shift. Every group is fitted from its own rows, and each one gets its own chart and its own table on the same worksheet.
Goal¶
Fit a trend line to ten months of demand for each of two regions, and see what happens when they are analyzed as one series instead.
Sample Data¶
Ten monthly demand figures from each of two regions, in row order within each region. Press Copy for Excel, then paste into a blank worksheet.
| Region | Demand |
|---|---|
| North | 127.7 |
| North | 134.7 |
| North | 132.4 |
| North | 133.4 |
| North | 137.4 |
| North | 150.2 |
| North | 149.9 |
| North | 153.4 |
| North | 169.2 |
| North | 174.8 |
| South | 153.3 |
| South | 142.0 |
| South | 145.0 |
| South | 142.1 |
| South | 131.0 |
| South | 140.7 |
| South | 136.9 |
| South | 146.8 |
| South | 131.2 |
| South | 126.6 |
Region is the grouping column. It is not a measurement and you do not tick it in the data column list.
Steps¶
-
Put the data in Excel
Press Copy for Excel, click cell A1 in a blank worksheet, and press Ctrl+V. Headers land in row 1 and the twenty rows in rows 2 through 21.
-
Launch the analysis
From the Excel ribbon, select QXL Stat Tools → Analysis Tools → Time Series → Trend Analysis.
-
Select your data
Select cells A1:B21.
-
Configure the analysis
On the Data tab, select the GroupBy radio button instead of Excel. The GroupBy group box appears; select Region in Available Items and press > to move it into GroupBy Order.
Tick Demand under Data Columns and leave Region unticked. On the Options tab leave Linear selected and Number of Forecasts: at 0.
Click Finish.
Result¶
One worksheet, with two chart and table blocks stacked down it. GroupBy does not produce a worksheet per group.
| North | South | |
|---|---|---|
| Equation | y = 118.5533 + 5.0467x | y = 149.9867 - 1.8958x |
| MAPE | 3.0896 | 2.8899 |
| MAD | 4.5053 | 4.0321 |
| MSD | 23.3409 | 30.6208 |
North is rising by about 5.05 a month. South is falling by about 1.90 a month. Both lines fit their own region closely: North misses by 4.51 on average and South by 4.03.
Each block's title carries the group name after the column name, as Trend Analysis for Demand - North. The separator is a space, hyphen, space.
What Pooling Destroys¶
Run the same twenty rows again with the Excel data source and no GroupBy, so the analysis sees one column of twenty values, North's ten followed by South's ten.
| North | South | Both as one series | |
|---|---|---|---|
| Slope per period | +5.0467 | -1.8958 | -0.1166 |
| MAPE | 3.0896 | 2.8899 | 6.7994 |
| MAD | 4.5053 | 4.0321 | 9.8887 |
| MSD | 23.3409 | 30.6208 | 157.8031 |
Pooled, the fitted equation is y = 144.1595 - 0.1166x: a slope of almost nothing. One region is climbing and the other is drifting down, and stacking them end to end produces a line that reports neither. It is not an average of the two slopes either; the answer depends on which region happened to be pasted first.
The accuracy measures show the damage plainly. MAD roughly doubles, from about 4.3 within a region to 9.89, and MSD goes from the low twenties and thirties to 157.80, because the line now misses badly at both ends of the combined series.
Stacking two series is not the same as having a longer one. Row order is the only x-axis a Time Series analysis has, so twenty rows always read as twenty consecutive periods. GroupBy is what tells the analysis that rows 11 through 20 are a second period 1 through 10, not periods 11 through 20.
Things to try next¶
- Add Number of Forecasts and re-run with GroupBy. Each region forecasts from its own line, so North's forecast rises and South's falls.
- Switch the analysis to Moving Average with the same GroupBy. Every Time Series analysis takes GroupBy the same way, and the two blocks are built the same way.
- Move Region back out of GroupBy Order before clicking Finish. With no grouping column the run falls back to the pooled behavior above, and nothing warns you that it has.
How GroupBy works here
Quantum XL produces one result per data column per group. Two groups and one measurement column give two blocks; two groups and three measurement columns would give six. The blocks stack vertically on a single worksheet with the chart on the left and the table to its right.