[1] "Database: "
[1] "Predifo"
[1] "file: "
[1] "Sales3"
Beispiel Sales
Jürgen Mayer
April 12, 2024
Im folgenden werden Forecasts von gruppierten oder hierarischen Saleszahlen erzeugt. Das entsprechende R script wird exportiert und kann dann weiterverwendet werden.
[1] "Database: "
[1] "Predifo"
[1] "file: "
[1] "Sales3"
Namen werden für die Standardisierung vereinheitlicht:
date
category category_2
value
Filespezifische Anpassung
`summarise()` has grouped output by 'category', 'category_2'. You can override
using the `.groups` argument.
`summarise()` has grouped output by 'category'. You can override using the
`.groups` argument.
# A tibble: 20 × 2
# Groups: category [5]
category category_2
<int> <int>
1 200 1
2 200 2
3 200 3
4 200 NA
5 210 1
6 210 2
7 210 3
8 210 NA
9 220 1
10 220 2
11 220 3
12 220 NA
13 230 1
14 230 2
15 230 3
16 230 NA
17 300 1
18 300 2
19 300 3
20 300 NA
# A tibble: 20 × 3
# Groups: category, category_2 [20]
category category_2 n
<int> <int> <int>
1 200 1 236
2 200 2 209
3 200 3 236
4 200 NA 6
5 210 1 228
6 210 2 214
7 210 3 227
8 210 NA 12
9 220 1 215
10 220 2 215
11 220 3 215
12 220 NA 13
13 230 1 221
14 230 2 196
15 230 3 221
16 230 NA 13
17 300 1 210
18 300 2 191
19 300 3 209
20 300 NA 30
category_2 ist teilweise NA –> entweder ersetzen durch 4 oder löschen.
# A tibble: 15 × 3
# Groups: category, category_2 [15]
category category_2 n
<int> <int> <int>
1 200 1 236
2 200 2 209
3 200 3 236
4 210 1 228
5 210 2 214
6 210 3 227
7 220 1 215
8 220 2 215
9 220 3 215
10 230 1 221
11 230 2 196
12 230 3 221
13 300 1 210
14 300 2 191
15 300 3 209
Man beachte die unterschiedlichen Skalierungen der Y-Achsen.
Loading required namespace: crayon
# A tsibble: 5,343 x 4 [1M]
# Key: category, category_2 [24]
Datum_ym category category_2 value
<mth> <int*> <int*> <dbl>
1 2002 Apr <aggregated> <aggregated> 600
2 2002 May <aggregated> <aggregated> 882
3 2002 Jun <aggregated> <aggregated> 1294
4 2002 Jul <aggregated> <aggregated> 4190
5 2002 Aug <aggregated> <aggregated> 721
6 2002 Sep <aggregated> <aggregated> 10970
7 2002 Oct <aggregated> <aggregated> 7124
8 2002 Nov <aggregated> <aggregated> 7771
9 2002 Dec <aggregated> <aggregated> 3472
10 2003 Jan <aggregated> <aggregated> 9208
# ℹ 5,333 more rows
method:
Warning: The future dataset is incomplete, incomplete out-of-sample data will be treated as missing.
1 observation is missing at 2021 Dec
.model | rmse | mase |
---|---|---|
base | 917.86 | 1.26 |
bu | 917.86 | 1.26 |
mint | 927.45 | 1.26 |
ols | 943.83 | 1.91 |
Warning: The future dataset is incomplete, incomplete out-of-sample data will be treated as missing.
1 observation is missing at 2021 Dec
# A tibble: 60 × 6
.model category category_2 .type rmse mase
<chr> <int*> <int*> <chr> <dbl> <dbl>
1 base 200 1 Test 171. 1.03
2 base 200 2 Test 547. 2.00
3 base 200 3 Test 765. 0.865
4 base 210 1 Test 576. 1.22
5 base 210 2 Test 67.9 0.371
6 base 210 3 Test 357. 0.578
7 base 220 1 Test 214. 1.45
8 base 220 2 Test 10.4 0.0152
9 base 220 3 Test 1941. 0.711
10 base 230 1 Test 1772. 1.96
# ℹ 50 more rows
.model | category | category_2 | .type | rmse | mase |
---|---|---|---|---|---|
base | 200 | 1 | Test | 170.67 | 1.03 |
base | 200 | 2 | Test | 547.42 | 2.00 |
base | 200 | 3 | Test | 764.74 | 0.87 |
base | 210 | 1 | Test | 575.77 | 1.22 |
base | 210 | 2 | Test | 67.92 | 0.37 |
base | 210 | 3 | Test | 357.27 | 0.58 |
base | 220 | 1 | Test | 213.64 | 1.45 |
base | 220 | 2 | Test | 10.41 | 0.02 |
base | 220 | 3 | Test | 1940.95 | 0.71 |
base | 230 | 1 | Test | 1772.13 | 1.96 |
base | 230 | 2 | Test | 14.01 | 0.95 |
base | 230 | 3 | Test | 558.07 | 2.52 |
base | 300 | 1 | Test | 4056.16 | 2.00 |
base | 300 | 2 | Test | 993.65 | 1.88 |
base | 300 | 3 | Test | 1725.10 | 1.32 |
bu | 200 | 1 | Test | 170.67 | 1.03 |
bu | 200 | 2 | Test | 547.42 | 2.00 |
bu | 200 | 3 | Test | 764.74 | 0.87 |
bu | 210 | 1 | Test | 575.77 | 1.22 |
bu | 210 | 2 | Test | 67.92 | 0.37 |
bu | 210 | 3 | Test | 357.27 | 0.58 |
bu | 220 | 1 | Test | 213.64 | 1.45 |
bu | 220 | 2 | Test | 10.41 | 0.02 |
bu | 220 | 3 | Test | 1940.95 | 0.71 |
bu | 230 | 1 | Test | 1772.13 | 1.96 |
bu | 230 | 2 | Test | 14.01 | 0.95 |
bu | 230 | 3 | Test | 558.07 | 2.52 |
bu | 300 | 1 | Test | 4056.16 | 2.00 |
bu | 300 | 2 | Test | 993.65 | 1.88 |
bu | 300 | 3 | Test | 1725.10 | 1.32 |
mint | 200 | 1 | Test | 167.71 | 1.01 |
mint | 200 | 2 | Test | 545.76 | 1.98 |
mint | 200 | 3 | Test | 810.67 | 0.95 |
mint | 210 | 1 | Test | 573.69 | 1.22 |
mint | 210 | 2 | Test | 57.60 | 0.31 |
mint | 210 | 3 | Test | 356.75 | 0.58 |
mint | 220 | 1 | Test | 212.78 | 1.44 |
mint | 220 | 2 | Test | 35.23 | 0.05 |
mint | 220 | 3 | Test | 2122.69 | 0.82 |
mint | 230 | 1 | Test | 1812.62 | 2.06 |
mint | 230 | 2 | Test | 14.01 | 0.96 |
mint | 230 | 3 | Test | 560.92 | 2.53 |
mint | 300 | 1 | Test | 4080.54 | 1.98 |
mint | 300 | 2 | Test | 973.89 | 1.85 |
mint | 300 | 3 | Test | 1586.87 | 1.19 |
ols | 200 | 1 | Test | 233.34 | 1.34 |
ols | 200 | 2 | Test | 579.76 | 3.17 |
ols | 200 | 3 | Test | 759.45 | 0.84 |
ols | 210 | 1 | Test | 588.34 | 1.24 |
ols | 210 | 2 | Test | 49.66 | 0.24 |
ols | 210 | 3 | Test | 414.65 | 0.74 |
ols | 220 | 1 | Test | 278.83 | 1.64 |
ols | 220 | 2 | Test | 132.90 | 0.21 |
ols | 220 | 3 | Test | 1923.16 | 0.71 |
ols | 230 | 1 | Test | 1784.82 | 1.96 |
ols | 230 | 2 | Test | 115.49 | 7.51 |
ols | 230 | 3 | Test | 700.56 | 3.31 |
ols | 300 | 1 | Test | 4060.95 | 2.00 |
ols | 300 | 2 | Test | 984.49 | 2.50 |
ols | 300 | 3 | Test | 1550.98 | 1.18 |
# A fable: 1,152 x 6 [1M]
# Key: category, category_2, .model [48]
category category_2 .model Datum_ym value .mean
<int*> <int*> <chr> <mth> <dist> <dbl>
1 200 1 ets 2021 Dec N(517, 15953) 517.
2 200 1 ets 2022 Jan N(619, 23071) 619.
3 200 1 ets 2022 Feb N(598, 21645) 598.
4 200 1 ets 2022 Mar N(599, 21890) 599.
5 200 1 ets 2022 Apr N(518, 16424) 518.
6 200 1 ets 2022 May N(492, 14929) 492.
7 200 1 ets 2022 Jun N(785, 38245) 785.
8 200 1 ets 2022 Jul N(707, 31251) 707.
9 200 1 ets 2022 Aug N(551, 19057) 551.
10 200 1 ets 2022 Sep N(566, 20242) 566.
# ℹ 1,142 more rows
Warning: The future dataset is incomplete, incomplete out-of-sample data will be treated as missing.
1 observation is missing at 2021 Dec
category | category_2 | .model | .type | RMSE | MAE | MAPE | MASE | RMSSE |
---|---|---|---|---|---|---|---|---|
200 | 1 | base | Test | 170.67 | 139.59 | 35.00 | 1.03 | 0.62 |
200 | 1 | bu | Test | 170.67 | 139.59 | 35.00 | 1.03 | 0.62 |
200 | 1 | mint | Test | 167.71 | 137.61 | 34.02 | 1.01 | 0.61 |
200 | 1 | ols | Test | 233.34 | 182.39 | 32.59 | 1.34 | 0.85 |
200 | 2 | base | Test | 547.42 | 183.86 | 112.45 | 2.00 | 1.65 |
200 | 2 | bu | Test | 547.42 | 183.86 | 112.45 | 2.00 | 1.65 |
200 | 2 | mint | Test | 545.76 | 182.21 | 107.53 | 1.98 | 1.64 |
200 | 2 | ols | Test | 579.76 | 292.38 | 462.24 | 3.17 | 1.74 |
200 | 3 | base | Test | 764.74 | 595.02 | 35.49 | 0.87 | 0.48 |
200 | 3 | bu | Test | 764.74 | 595.02 | 35.49 | 0.87 | 0.48 |
200 | 3 | mint | Test | 810.67 | 655.59 | 37.11 | 0.95 | 0.51 |
200 | 3 | ols | Test | 759.45 | 577.65 | 36.28 | 0.84 | 0.48 |
200 | base | Test | 1028.44 | 864.74 | 36.80 | 1.03 | 0.59 | |
200 | bu | Test | 983.14 | 792.42 | 35.48 | 0.95 | 0.57 | |
200 | mint | Test | 981.67 | 811.99 | 35.56 | 0.97 | 0.57 | |
200 | ols | Test | 1039.93 | 878.01 | 36.49 | 1.05 | 0.60 | |
210 | 1 | base | Test | 575.77 | 444.66 | 104.85 | 1.22 | 1.22 |
210 | 1 | bu | Test | 575.77 | 444.66 | 104.85 | 1.22 | 1.22 |
210 | 1 | mint | Test | 573.69 | 443.26 | 102.80 | 1.22 | 1.21 |
210 | 1 | ols | Test | 588.34 | 450.25 | 80.29 | 1.24 | 1.24 |
210 | 2 | base | Test | 67.92 | 63.36 | 355.83 | 0.37 | 0.16 |
210 | 2 | bu | Test | 67.92 | 63.36 | 355.83 | 0.37 | 0.16 |
210 | 2 | mint | Test | 57.60 | 52.47 | 287.84 | 0.31 | 0.13 |
210 | 2 | ols | Test | 49.66 | 41.44 | 194.48 | 0.24 | 0.11 |
210 | 3 | base | Test | 357.27 | 248.10 | 29.64 | 0.58 | 0.59 |
210 | 3 | bu | Test | 357.27 | 248.10 | 29.64 | 0.58 | 0.59 |
210 | 3 | mint | Test | 356.75 | 246.84 | 29.54 | 0.58 | 0.59 |
210 | 3 | ols | Test | 414.65 | 318.47 | 39.36 | 0.74 | 0.68 |
210 | base | Test | 683.13 | 584.07 | 43.59 | 0.74 | 0.66 | |
210 | bu | Test | 691.45 | 602.13 | 44.01 | 0.77 | 0.66 | |
210 | mint | Test | 684.32 | 588.13 | 42.82 | 0.75 | 0.66 | |
210 | ols | Test | 707.83 | 593.88 | 41.66 | 0.76 | 0.68 | |
220 | 1 | base | Test | 213.64 | 166.47 | 37.98 | 1.45 | 1.41 |
220 | 1 | bu | Test | 213.64 | 166.47 | 37.98 | 1.45 | 1.41 |
220 | 1 | mint | Test | 212.78 | 165.61 | 37.68 | 1.44 | 1.41 |
220 | 1 | ols | Test | 278.83 | 188.89 | 32.29 | 1.64 | 1.85 |
220 | 2 | base | Test | 10.41 | 9.08 | Inf | 0.02 | 0.01 |
220 | 2 | bu | Test | 10.41 | 9.08 | Inf | 0.02 | 0.01 |
220 | 2 | mint | Test | 35.23 | 29.44 | Inf | 0.05 | 0.02 |
220 | 2 | ols | Test | 132.90 | 122.51 | Inf | 0.21 | 0.09 |
220 | 3 | base | Test | 1940.95 | 1531.00 | 38.91 | 0.71 | 0.43 |
220 | 3 | bu | Test | 1940.95 | 1531.00 | 38.91 | 0.71 | 0.43 |
220 | 3 | mint | Test | 2122.69 | 1774.99 | 47.61 | 0.82 | 0.47 |
220 | 3 | ols | Test | 1923.16 | 1536.31 | 40.17 | 0.71 | 0.43 |
220 | base | Test | 2135.99 | 1624.00 | 34.33 | 0.66 | 0.44 | |
220 | bu | Test | 2008.43 | 1542.81 | 33.94 | 0.63 | 0.42 | |
220 | mint | Test | 2205.53 | 1801.98 | 41.22 | 0.74 | 0.46 | |
220 | ols | Test | 2180.52 | 1675.25 | 35.83 | 0.68 | 0.45 | |
230 | 1 | base | Test | 1772.13 | 1370.52 | 80.22 | 1.96 | 1.05 |
230 | 1 | bu | Test | 1772.13 | 1370.52 | 80.22 | 1.96 | 1.05 |
230 | 1 | mint | Test | 1812.62 | 1442.02 | 91.13 | 2.06 | 1.08 |
230 | 1 | ols | Test | 1784.82 | 1374.28 | 80.62 | 1.96 | 1.06 |
230 | 2 | base | Test | 14.01 | 11.60 | 83.90 | 0.95 | 0.72 |
230 | 2 | bu | Test | 14.01 | 11.60 | 83.90 | 0.95 | 0.72 |
230 | 2 | mint | Test | 14.01 | 11.64 | 83.03 | 0.96 | 0.72 |
230 | 2 | ols | Test | 115.49 | 91.41 | 514.57 | 7.51 | 5.95 |
230 | 3 | base | Test | 558.07 | 430.12 | 32.84 | 2.52 | 2.22 |
230 | 3 | bu | Test | 558.07 | 430.12 | 32.84 | 2.52 | 2.22 |
230 | 3 | mint | Test | 560.92 | 431.84 | 32.98 | 2.53 | 2.23 |
230 | 3 | ols | Test | 700.56 | 565.00 | 44.00 | 3.31 | 2.79 |
230 | base | Test | 2065.64 | 1665.56 | 58.49 | 2.13 | 1.21 | |
230 | bu | Test | 1855.63 | 1494.27 | 48.80 | 1.91 | 1.09 | |
230 | mint | Test | 1941.22 | 1573.63 | 53.40 | 2.01 | 1.14 | |
230 | ols | Test | 1980.98 | 1590.99 | 54.00 | 2.03 | 1.16 | |
300 | 1 | base | Test | 4056.16 | 3165.68 | 94.01 | 2.00 | 1.54 |
300 | 1 | bu | Test | 4056.16 | 3165.68 | 94.01 | 2.00 | 1.54 |
300 | 1 | mint | Test | 4080.54 | 3130.50 | 89.88 | 1.98 | 1.54 |
300 | 1 | ols | Test | 4060.95 | 3164.90 | 91.55 | 2.00 | 1.54 |
300 | 2 | base | Test | 993.65 | 453.69 | 64.58 | 1.88 | 0.84 |
300 | 2 | bu | Test | 993.65 | 453.69 | 64.58 | 1.88 | 0.84 |
300 | 2 | mint | Test | 973.89 | 446.91 | 67.14 | 1.85 | 0.83 |
300 | 2 | ols | Test | 984.49 | 604.38 | 222.55 | 2.50 | 0.84 |
300 | 3 | base | Test | 1725.10 | 1509.43 | 27.16 | 1.32 | 0.70 |
300 | 3 | bu | Test | 1725.10 | 1509.43 | 27.16 | 1.32 | 0.70 |
300 | 3 | mint | Test | 1586.87 | 1360.56 | 25.10 | 1.19 | 0.64 |
300 | 3 | ols | Test | 1550.98 | 1351.99 | 24.93 | 1.18 | 0.63 |
300 | base | Test | 5018.68 | 4003.58 | 35.13 | 1.64 | 1.32 | |
300 | bu | Test | 5170.17 | 4159.17 | 36.71 | 1.70 | 1.36 | |
300 | mint | Test | 5058.02 | 4096.28 | 35.53 | 1.68 | 1.33 | |
300 | ols | Test | 5049.66 | 4082.23 | 35.61 | 1.67 | 1.32 | |
1 | base | Test | 4596.88 | 3863.83 | 44.14 | 2.06 | 1.58 | |
1 | bu | Test | 4739.74 | 4047.91 | 47.28 | 2.16 | 1.62 | |
1 | mint | Test | 4710.45 | 4008.09 | 46.56 | 2.14 | 1.61 | |
1 | ols | Test | 4808.83 | 4090.50 | 44.70 | 2.18 | 1.65 | |
2 | base | Test | 1028.97 | 547.11 | 139.01 | 0.66 | 0.56 | |
2 | bu | Test | 1035.44 | 545.02 | 131.75 | 0.66 | 0.56 | |
2 | mint | Test | 1036.32 | 548.49 | 105.95 | 0.67 | 0.56 | |
2 | ols | Test | 1241.20 | 933.32 | 288.14 | 1.13 | 0.67 | |
3 | base | Test | 3533.28 | 2839.46 | 23.43 | 0.81 | 0.60 | |
3 | bu | Test | 3954.65 | 3390.99 | 25.53 | 0.97 | 0.67 | |
3 | mint | Test | 3886.42 | 3350.61 | 25.17 | 0.96 | 0.66 | |
3 | ols | Test | 3632.13 | 3035.59 | 23.95 | 0.87 | 0.62 | |
base | Test | 8315.66 | 7057.24 | 30.07 | 1.47 | 1.19 | ||
bu | Test | 7929.37 | 6869.55 | 30.42 | 1.43 | 1.14 | ||
mint | Test | 7935.16 | 6776.70 | 29.77 | 1.41 | 1.14 | ||
ols | Test | 7922.62 | 6733.29 | 29.39 | 1.40 | 1.13 |
Die generierten Daten werden exportiert.
Möglichkeiten:
lokales file
Speichern in SQL Server
exp_fc_h wurde exportier