MentDB WeakMQL Recipes
← innov-ai.com All profiles Portal
Profiles / AI & prediction
FE

Forecaster & econometrician

Linear and multiple regressions, confidence intervals, ANOVA and polynomial trends.

10 recipes on this page · open in the interactive view

Recipes
  1. Sales trend and next quarter forecast
  2. ANOVA of leads vs advertising spend
  3. Demand model and price elasticity
  4. Calibrate a two-rate electricity tariff
  5. Polynomial trend of user growth
  6. Trend forecast straight from the sales database
  7. Cost model through the origin
  8. Promotion uplift from the weekly sales table
  9. Forecast accuracy and bias test
  10. Seasonal indices by the ratio-to-moving-average method
executed on MentDB

Sales trend and next quarter forecast

Fit a linear trend on 12 months of sales, compute the slope with its 95 % confidence half-width and R2, forecast months 13 to 15 and draw the measures, the trend line and the forecast bars, with the figures in the chart title.

Pipeline
01 · fitLeast squares trendpa rl load_from_json→02 · coeffsSlope, intercept, CIpa rl slope_confidence_interval→03 · qualityR2pa rl r_square→04 · forecastMonths 13 to 15pa rl predict→05 · drawPoints, line and barsplot chart
Script
admin
#1. Twelve months of sales in thousands of EUR, as x,y pairs for the regression;
-> "[mx]" "[1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12]";
-> "[my]" "[102.4, 108.1, 111.9, 118.6, 121.0, 127.3, 131.8, 135.2, 142.7, 146.1, 151.9, 155.4]";
json load "rx_my" [my];
-> "[sales]" "[";
for (-> "[i]" 0) (< [i] 12) (++ "[i]") {
  -> "[sales]" (concat [sales] (if (== [i] 0) {""} {", "}) "[" (+ [i] 1) ", " (json select "rx_my" (concat "/[" [i] "]")) "]");
};
-> "[sales]" (concat [sales] "]");
json unload "rx_my";
#2. Fit the trend y = a + b*month;
pa rl load_from_json "rx_sales" [sales];
-> "[b]" (math round (pa rl slope "rx_sales") 3);
-> "[a]" (math round (pa rl intercept "rx_sales") 3);
-> "[ci]" (math round (pa rl slope_confidence_interval "rx_sales") 3);
-> "[r2]" (math round (pa rl r_square "rx_sales") 4);
-> "[n]" (pa rl count "rx_sales");
#3. Forecast the next quarter, months 13 to 15;
-> "[p13]" (math round (pa rl predict "rx_sales" 13) 1);
-> "[p14]" (math round (pa rl predict "rx_sales" 14) 1);
-> "[p15]" (math round (pa rl predict "rx_sales" 15) 1);
pa rl close "rx_sales";
-> "[title]" (concat "n=" [n] ": sales = " [a] " + " [b] " x month, slope 95% CI +/- " [ci] ", R2=" [r2] ", next quarter " (+ (+ [p13] [p14]) [p15]) " kEUR");
#4. Chart: measures, trend line and forecast bars;
plot chart (concat "{\"title\": \"" [title] "\", \"x_label\": \"month\", \"y_label\": \"kEUR\", \"series\": [{\"name\": \"sales\", \"type\": \"points\", \"x\": " [mx] ", \"y\": " [my] "}, {\"name\": \"trend\", \"x\": [1, 15], \"y\": [" (+ [a] [b]) ", " [p15] "]}, {\"name\": \"forecast\", \"type\": \"bars\", \"x\": [13, 14, 15], \"y\": [" [p13] ", " [p14] ", " [p15] "]}]}")
Result
mentdb
The chart is shown in the Plot tab of the editor.
Go further
  • Fit the trend directly on a table with pa rl load and a SQL query.
  • Compare with stat fit and the models exp, log or power.
executed on MentDB

ANOVA of leads vs advertising spend

Feed a regression one week at a time with ad spend and leads, then print the ANOVA decomposition (SST = SSR + SSE), the standard errors, the p-value of the slope and its 90 % confidence half-width.

Pipeline
01 · streamEmpty model fed point by pointpa rl add_data→02 · anovaSSR, SSE, SST, Sxx, MSEpa rl sum_squares→03 · inferenceStandard errors and p-valuepa rl significance→04 · interval90 % confidence half-widthpa rl slope_confidence_interval
Script
admin
#1. Weekly ad spend in kEUR and leads, the model is fed one week at a time;
pa rl load_empty "rx_ads";
json load "rx_x" "[2.0, 3.5, 1.0, 4.2, 5.0, 2.8, 6.1, 3.9]";
json load "rx_y" "[41, 62, 25, 70, 88, 50, 97, 66]";
for (-> "[i]" 0) (< [i] (json count "rx_x" /)) (++ "[i]") {
  pa rl add_data "rx_ads" (json select "rx_x" (concat "/[" [i] "]")) (json select "rx_y" (concat "/[" [i] "]"));
};
json unload "rx_x"; json unload "rx_y";
#2. ANOVA table: SST = SSR + SSE, and inference on the slope;
json load "rx_anova" "{}";
json iobject "rx_anova" / "leads_per_kEUR" (math round (pa rl slope "rx_ads") 3) NUM;
json iobject "rx_anova" / "SSR" (math round (pa rl sum_squares "rx_ads") 2) NUM;
json iobject "rx_anova" / "SSE" (math round (pa rl sum_squared_errors "rx_ads") 2) NUM;
json iobject "rx_anova" / "SST" (math round (pa rl total_sum_squares "rx_ads") 2) NUM;
json iobject "rx_anova" / "Sxx" (math round (pa rl x_sum_squares "rx_ads") 4) NUM;
json iobject "rx_anova" / "MSE" (math round (pa rl mean_square_error "rx_ads") 4) NUM;
json iobject "rx_anova" / "r" (math round (pa rl r "rx_ads") 5) NUM;
json iobject "rx_anova" / "s_b1" (math round (pa rl slope_std_err "rx_ads") 4) NUM;
json iobject "rx_anova" / "s_b0" (math round (pa rl intercept_std_err "rx_ads") 4) NUM;
json iobject "rx_anova" / "p_value" (pa rl significance "rx_ads") NUM;
json iobject "rx_anova" / "slope_ci90_half_width" (math round (pa rl slope_confidence_interval "rx_ads" 0.1) 4) NUM;
json iobject "rx_anova" / "models_in_memory" (pa rl show) ARRAY;
json iobject "rx_anova" / "rx_ads_exists" (pa rl exist "rx_ads") NUM;
pa rl close "rx_ads";
json doc "rx_anova"
Result
mentdb
{
  "p_value": 0.00000023019891126629943,
  "models_in_memory": [
    "rx_ads"
  ],
  "SSE": 35.49,
  "rx_ads_exists": 1.0,
  "s_b1": 0.5636,
  "s_b0": 2.1843,
  "Sxx": 18.6188,
  "r": 0.99548,
  "slope_ci90_half_width": 1.0952,
  "SSR": 3898.39,
  "SST": 3933.88,
  "leads_per_kEUR": 14.47,
  "MSE": 5.9146
}
Go further
  • SSR + SSE = SST: check it on the result.
  • pa rl close_all frees every regression of the session.
executed on MentDB

Demand model and price elasticity

Explain weekly demand by the unit price and the promo budget with an OLS multiple regression, test the price coefficient (t statistic), compute the price elasticity at the mean point and simulate a price and promo scenario.

Pipeline
01 · fitOLS with two regressorspa rm load_from_json→02 · coeffsCoefficients and standard errorspa rm estimate_regression_parameters_standard_errors→03 · elasticityElasticity at the meanstat mean→04 · simulateWhat-if scenariopa rm predict
Script
admin
#1. Weekly demand in units vs unit price in EUR and promo budget in kEUR;
-> "[X]" "[[9.9, 2.0], [9.5, 3.0], [10.5, 1.0], [8.9, 4.0], [9.2, 2.5], [10.9, 0.5], [8.5, 5.0], [9.8, 3.5], [10.2, 1.5], [8.7, 4.5]]";
-> "[Y]" "[520, 585, 455, 660, 575, 410, 720, 570, 490, 690]";
pa rm load_from_json "rx_dem" [X] [Y];
#2. OLS coefficients b0, b_price, b_promo and the t statistic of the price;
json load "rx_b" (pa rm estimate_regression_parameters "rx_dem");
json load "rx_se" (pa rm estimate_regression_parameters_standard_errors "rx_dem");
-> "[t_price]" (math round (/ (json select "rx_b" "/[1]") (json select "rx_se" "/[1]")) 2);
#3. Price elasticity at the mean point;
-> "[p_mean]" (stat mean "[9.9, 9.5, 10.5, 8.9, 9.2, 10.9, 8.5, 9.8, 10.2, 8.7]");
-> "[elast]" (math round (/ (* (json select "rx_b" "/[1]") [p_mean]) (stat mean [Y])) 2);
#4. What-if: price 9.00 EUR with a 4 kEUR promo;
-> "[pred]" (math round (pa rm predict "rx_dem" "[9.0, 4.0]") 0);
-> "[r2]" (math round (pa rm calculate_r_squared "rx_dem") 4);
-> "[r2a]" (math round (pa rm calculate_adjusted_r_squared "rx_dem") 4);
-> "[eq]" (concat "demand = " (math round (json select "rx_b" "/[0]") 1) " " (math round (json select "rx_b" "/[1]") 2) " x price + " (math round (json select "rx_b" "/[2]") 2) " x promo");
pa rm close "rx_dem";
json unload "rx_b"; json unload "rx_se";
concat [eq] " | t(price)=" [t_price] ", R2=" [r2] ", adjusted R2=" [r2a] " | price elasticity=" [elast] " | 9.00 EUR + 4 kEUR promo: " [pred] " units/week"
Result
mentdb
demand = 1098.7 -64.99 x price + 33.94 x promo | t(price)=-7.66, R2=0.9965, adjusted R2=0.9955 | price elasticity=-1.1 | 9.00 EUR + 4 kEUR promo: 650 units/week
Go further
  • Load up to five regressors from a table with pa rm load.
  • An elasticity below -1 means that a price cut increases revenue.
executed on MentDB

Calibrate a two-rate electricity tariff

Recover the fixed monthly fee and the peak and off-peak prices per kWh from eight bills with a multiple regression, with the standard errors of the prices and the fit diagnostics (residual variance, regression standard error, worst residual).

Pipeline
01 · fitBills vs kWh peak and off-peakpa rm load_from_json→02 · pricesFee, prices, covariance matrixpa rm estimate_regression_parameters_variance→03 · residualsResiduals and sums of squarespa rm estimate_residuals→04 · errorsError variance and standard errorpa rm estimate_regression_standard_error
Script
admin
#1. Monthly kWh at peak and off-peak hours vs the bill in EUR;
-> "[kwh]" "[[210, 340], [185, 300], [250, 410], [160, 280], [230, 360], [275, 450], [198, 330], [240, 395]]";
-> "[bill]" "[90.9, 81.6, 105.8, 73.0, 97.0, 115.4, 86.6, 102.0]";
pa rm load_from_json "rx_tariff" [kwh] [bill];
#2. Fee and unit prices with the covariance matrix of the estimates;
json load "rx_b" (pa rm estimate_regression_parameters "rx_tariff");
json load "rx_v" (pa rm estimate_regression_parameters_variance "rx_tariff");
-> "[res]" (pa rm estimate_residuals "rx_tariff");
json load "rx_out" "{}";
json iobject "rx_out" / "fixed_fee" (math round (json select "rx_b" "/[0]") 2) NUM;
json iobject "rx_out" / "p_peak" (math round (json select "rx_b" "/[1]") 4) NUM;
json iobject "rx_out" / "se_p_peak" (math round (math sqrt (json select "rx_v" "/[1]/[1]")) 4) NUM;
json iobject "rx_out" / "p_offpeak" (math round (json select "rx_b" "/[2]") 4) NUM;
json iobject "rx_out" / "se_p_offpeak" (math round (math sqrt (json select "rx_v" "/[2]/[2]")) 4) NUM;
#3. Goodness of fit;
-> "[ssr]" (pa rm calculate_residual_sum_of_squares "rx_tariff");
-> "[ssto]" (pa rm calculate_total_sum_of_squares "rx_tariff");
json iobject "rx_out" / "R2_check" (math round (- 1 (/ [ssr] [ssto])) 5) NUM;
json iobject "rx_out" / "error_variance" (math round (pa rm estimate_error_variance "rx_tariff") 4) NUM;
json iobject "rx_out" / "regression_std_err_EUR" (math round (pa rm estimate_regression_standard_error "rx_tariff") 3) NUM;
json iobject "rx_out" / "var_bill" (math round (pa rm estimate_regressand_variance "rx_tariff") 2) NUM;
json iobject "rx_out" / "worst_residual_EUR" (math round (stat max (concat "[" (math abs (stat min [res])) ", " (stat max [res]) "]")) 3) NUM;
json iobject "rx_out" / "loaded_models" (pa rm show) ARRAY;
json iobject "rx_out" / "exists" (pa rm exist "rx_tariff") NUM;
pa rm close "rx_tariff";
json doc "rx_out"
Result
mentdb
{
  "R2_check": 0.99968,
  "loaded_models": [
    "rx_tariff"
  ],
  "p_peak": 0.2781,
  "error_variance": 0.0853,
  "var_bill": 189.9,
  "worst_residual_EUR": 0.347,
  "fixed_fee": 12.12,
  "se_p_peak": 0.0674,
  "regression_std_err_EUR": 0.292,
  "exists": 1.0,
  "p_offpeak": 0.0591,
  "se_p_offpeak": 0.044
}
Go further
executed on MentDB

Polynomial trend of user growth

Fit a quadratic trend on 10 months of active users, forecast months 11 to 15 and draw the data, the fitted curve and the forecast points on the same chart.

Pipeline
01 · fitQuadratic least squarespa polynomial_curve_fit_get_coeff→02 · forecastMonths 11 to 14pa polynomial_curve_fit_eval_incr→03 · horizonMonth 15pa polynomial_curve_fit_eval→04 · drawData and fitplot fit→05 · overlayForecast pointsplot add
Script
admin
#1. Monthly active users in thousands, growth is accelerating;
-> "[x]" "[1, 2, 3, 4, 5, 6, 7, 8, 9, 10]";
-> "[y]" "[12.1, 12.9, 14.2, 16.0, 18.1, 20.9, 23.8, 27.4, 31.2, 35.5]";
-> "[pts]" "[[1, 12.1], [2, 12.9], [3, 14.2], [4, 16.0], [5, 18.1], [6, 20.9], [7, 23.8], [8, 27.4], [9, 31.2], [10, 35.5]]";
#2. Quadratic trend c0 + c1*x + c2*x^2 by least squares;
-> "[c]" (pa polynomial_curve_fit_get_coeff [pts] 2);
json load "rx_c" [c];
-> "[f]" (concat (json select "rx_c" "/[0]") "+(" (json select "rx_c" "/[1]") ")*x+(" (json select "rx_c" "/[2]") ")*x^2");
#3. Forecast months 11 to 14 with a step of 1, split into x and y arrays;
json load "rx_fc" (pa polynomial_curve_fit_eval_incr [c] 11 14 1);
-> "[fx]" "["; -> "[fy]" "[";
for (-> "[i]" 0) (< [i] (json count "rx_fc" /)) (++ "[i]") {
  -> "[sep]" (if (== [i] 0) {""} {", "});
  -> "[fx]" (concat [fx] [sep] (json select "rx_fc" (concat "/[" [i] "]/[0]")));
  -> "[fy]" (concat [fy] [sep] (math round (json select "rx_fc" (concat "/[" [i] "]/[1]")) 1));
};
#4. Month 15 evaluated on its own, end of the planning horizon;
-> "[m15]" (pa polynomial_curve_fit_eval [c] 15);
-> "[fx]" (concat [fx] ", 15]"); -> "[fy]" (concat [fy] ", " (math round [m15] 1) "]");
json unload "rx_c"; json unload "rx_fc";
#5. Data and fitted trend, then the forecast points on the same chart;
plot fit [x] [y] [f] "x";
plot add (plot points [fx] [fy])
Result
mentdb
The chart is shown in the Plot tab of the editor.
Go further
  • Try degree 3 and compare the forecasts: higher degrees diverge fast outside the data.
  • pa xy_scatter opens a scatter chart of the raw points.
executed on MentDB

Trend forecast straight from the sales database

The monthly sales are in a SQL table. The script fits the linear trend directly on a SELECT, forecasts the next quarter (months 13 to 15), prepares the scatter plot of the history for the editor and cleans up.

Pipeline
01 · historyMonthly sales tablesql dml→02 · fitTrend from a SELECTpa rl load→03 · forecastMonths 13 to 15pa rl predict→04 · registryModel in memorypa rl show→05 · scatterScatter of the historypa xy_scatter
Script
admin
#1. Monthly sales history in the sales database;
cm set "tuto_p4h_sales" {execute "db.h2.embedded.config.get"
	"[database]" "./tmp/tuto_p4h_db/sales"
	"[user]" "sa"
	"[password]" "sa"
;};
sql connect "rx_p4h_sales" {cm get "tuto_p4h_sales";};
sql dml "rx_p4h_sales" "create table monthly (month_no int primary key, units double)";
sql dml "rx_p4h_sales" "insert into monthly values (1, 1210), (2, 1185), (3, 1302), (4, 1350), (5, 1388), (6, 1421), (7, 1405), (8, 1497), (9, 1530), (10, 1562), (11, 1610), (12, 1648)";
#2. Linear trend fitted directly on the SQL query;
pa rl load "rx_p4h_trend" "tuto_p4h_sales" "month_no" "units" "select month_no, units from monthly order by month_no";
-> "[q1]" (+ (pa rl predict "rx_p4h_trend" 13) (pa rl predict "rx_p4h_trend" 14) (pa rl predict "rx_p4h_trend" 15));
-> "[report]" (concat "trend +" (math round (pa rl slope "rx_p4h_trend") 1) " units/month, R2 " (math round (pa rl r_square "rx_p4h_trend") 3) ", " (pa rl count "rx_p4h_trend") " months | next quarter forecast: " (math round [q1] 0) " units | model in memory: " (string contains (pa rl show) "rx_p4h_trend"));
#3. Scatter of the history for the editor, then clean up;
-> "[scatter]" (pa xy_scatter "tuto_p4h_sales" "month_no" "units" "select month_no, units from monthly");
pa rl close "rx_p4h_trend";
sql dml "rx_p4h_sales" "drop table monthly";
sql disconnect "rx_p4h_sales";
cm remove "tuto_p4h_sales";
file delete "tmp/tuto_p4h_db/sales.mv.db";
file delete "tmp/tuto_p4h_db";
[report]
Result
mentdb
trend +40.8 units/month, R2 0.971, 12 months | next quarter forecast: 5195 units | model in memory: 1
Go further
  • Return the [scatter] variable as the last statement to see the points in the editor.
  • Give an interval to the forecast with pa rl slope_confidence_interval.
  • Write the forecast back with sql dml insert into a forecast table.
executed on MentDB

Cost model through the origin

A delivery round costs a.km + b.stops with no fixed fee. The script fits the model with an intercept (to check the fee is near 0), refits it through the origin and quotes a 60 km round with 10 stops.

Pipeline
01 · fitModel with an interceptpa rm load_from_json→02 · checkFixed fee and adjusted R2pa rm calculate_adjusted_r_squared→03 · originRegression through the originpa rm set_no_intercept→04 · ratesEUR per km and per stoppa rm estimate_regression_parameters→05 · quoteQuote of a new roundpa rm predict
Script
admin
#1. Delivery rounds: km, stops and cost in EUR, no fixed fee expected;
-> "[X]" "[[42, 8], [65, 12], [30, 5], [88, 15], [51, 9], [73, 14], [25, 6]]";
-> "[y]" "[61.5, 94.2, 43.0, 124.8, 73.9, 106.1, 39.8]";
#2. Model with an intercept;
pa rm load_from_json "rx_p4h_cost" [X] [y];
json load "rx_p4h_w" (pa rm estimate_regression_parameters "rx_p4h_cost");
-> "[r2_with]" (pa rm calculate_adjusted_r_squared "rx_p4h_cost");
#3. Same data, regression through the origin;
pa rm close "rx_p4h_cost";
pa rm load_from_json "rx_p4h_cost" [X] [y];
pa rm set_no_intercept "rx_p4h_cost" true;
json load "rx_p4h_b" (pa rm estimate_regression_parameters "rx_p4h_cost");
-> "[se]" (pa rm estimate_regression_parameters_standard_errors "rx_p4h_cost");
-> "[quote]" (pa rm predict "rx_p4h_cost" "[60, 10]");
pa rm close "rx_p4h_cost";
concat "with intercept: fixed fee " (math round (json select "rx_p4h_w" "/[0]") 2) " EUR, adj R2 " (math round [r2_with] 4) " | through the origin: " (math round (json select "rx_p4h_b" "/[0]") 3) " EUR/km + " (math round (json select "rx_p4h_b" "/[1]") 3) " EUR/stop | quote 60 km, 10 stops: " (math round [quote] 2) " EUR"
Result
mentdb
with intercept: fixed fee 1.52 EUR, adj R2 0.9999 | through the origin: 1.062 EUR/km + 2.103 EUR/stop | quote 60 km, 10 stops: 84.73 EUR
Go further
executed on MentDB

Promotion uplift from the weekly sales table

Ten weeks of sales with price, promotion flag and temperature are in a database. The script fits units = b0 + b1.price + b2.promo + b3.temp from SQL and forecasts a planned promotion week against the same week without promotion.

Pipeline
01 · historyWeekly sales tablesql dml→02 · fit3 drivers from a SELECTpa rm load→03 · effectsPromo and price effectspa rm estimate_regression_parameters→04 · qualityR2pa rm calculate_r_squared→05 · planPromo week vs baselinepa rm predict
Script
admin
#1. Weekly sales with price, promotion flag and temperature;
cm set "tuto_p4h_retail" {execute "db.h2.embedded.config.get"
	"[database]" "./tmp/tuto_p4h_db/retail"
	"[user]" "sa"
	"[password]" "sa"
;};
sql connect "rx_p4h_retail" {cm get "tuto_p4h_retail";};
sql dml "rx_p4h_retail" "create table weeks (wk int primary key, price double, promo double, temp_c double, units double)";
sql dml "rx_p4h_retail" "insert into weeks values (1, 2.49, 0, 14, 820), (2, 2.49, 1, 15, 1105), (3, 2.29, 0, 17, 910), (4, 2.59, 0, 19, 760), (5, 2.29, 1, 21, 1240), (6, 2.49, 0, 22, 845), (7, 2.39, 1, 18, 1150), (8, 2.59, 0, 16, 735), (9, 2.29, 0, 23, 960), (10, 2.39, 1, 24, 1235)";
#2. Multiple regression units = b0 + b1.price + b2.promo + b3.temp from SQL;
pa rm load "rx_p4h_promo" "tuto_p4h_retail" "price" "promo" "temp_c" "" "" "units" "select * from weeks";
json load "rx_p4h_beta" (pa rm estimate_regression_parameters "rx_p4h_promo");
-> "[r2]" (pa rm calculate_r_squared "rx_p4h_promo");
#3. Plan: promo week at 2.39 EUR and 20 C;
-> "[plan]" (pa rm predict "rx_p4h_promo" "[2.39, 1, 20]");
-> "[base]" (pa rm predict "rx_p4h_promo" "[2.39, 0, 20]");
pa rm close "rx_p4h_promo";
sql dml "rx_p4h_retail" "drop table weeks";
sql disconnect "rx_p4h_retail";
cm remove "tuto_p4h_retail";
file delete "tmp/tuto_p4h_db/retail.mv.db";
file delete "tmp/tuto_p4h_db";
concat "promo uplift +" (math round (json select "rx_p4h_beta" "/[2]") 0) " units/week | price effect " (math round (json select "rx_p4h_beta" "/[1]") 0) " units per EUR | R2 " (math round [r2] 3) " | planned week: " (math round [plan] 0) " units vs " (math round [base] 0) " without promo"
Result
mentdb
promo uplift +302 units/week | price effect -535 units per EUR | R2 0.993 | planned week: 1186 units vs 884 without promo
Go further
executed on MentDB

Forecast accuracy and bias test

The demand forecast of the last 10 weeks is compared with the actual demand. The script computes MAE, RMSE and MAPE, then a z-test of the mean error (known error sigma 25 units) to detect a biased model.

Pipeline
01 · errorsActual - forecastmatrix sub→02 · rmseRoot mean square errorstat sum_sq→03 · biasH0: mean error = 0stat ztest→04 · decideRecalibrate or notif
Script
admin
#1. Last 10 weeks: actual demand and the forecast of the model;
-> "[act]" "[520, 610, 575, 640, 590, 700, 655, 610, 680, 720]";
-> "[fc]" "[505, 590, 600, 615, 610, 660, 640, 630, 650, 690]";
-> "[err]" (matrix sub (concat "[" [act] "]") (concat "[" [fc] "]"));
-> "[e]" (string replace (string replace [err] "[[" "[") "]]" "]");
#2. Accuracy: MAE, RMSE, MAPE;
-> "[mae]" 0; -> "[ape]" 0;
json load "rx_p4h_e" [e]; json load "rx_p4h_a" [act];
for (-> "[i]" 0) (< [i] 10) (++ "[i]") {
	-> "[ei]" (json select "rx_p4h_e" (concat "/[" [i] "]"));
	-> "[mae]" (+ [mae] (math abs [ei]));
	-> "[ape]" (+ [ape] (/ (math abs [ei]) (json select "rx_p4h_a" (concat "/[" [i] "]"))));
};
-> "[rmse]" (math sqrt (/ (stat sum_sq [e]) 10));
#3. Bias test: mean error = 0 with the known error sigma of 25 units;
json load "rx_p4h_z" (stat ztest [e] 0 25);
concat "MAE " (/ [mae] 10) " | RMSE " (math round [rmse] 1) " | MAPE " (math round (* 10 [ape]) 2) "% | mean error " (stat mean [e]) " z=" (math round (json select "rx_p4h_z" "/z") 3) " p=" (math round (json select "rx_p4h_z" "/p_value") 4) (if (< (json select "rx_p4h_z" "/p_value") 0.05) {" -> biased forecast, recalibrate"} {" -> no significant bias"})
Result
mentdb
MAE 24 | RMSE 25.1 | MAPE 3.77% | mean error 11 z=1.391 p=0.1641 -> no significant bias
Go further
  • When sigma is unknown, use stat ttest_1 on the errors.
  • Track the tracking signal (sum of errors / MAE) week after week.
  • Plot actual and forecast with plot chart and two series.
executed on MentDB

Seasonal indices by the ratio-to-moving-average method

Three years of quarterly revenue are decomposed: centred 2x4 moving average, ratio of each quarter to the trend, mean ratio by quarter normalised to a mean of 1. These indices deseasonalise the series before the trend forecast.

Pipeline
01 · trendCentred moving average 2x4json select→02 · ratioRatio to the trend by quarterjson iarray→03 · indexMean ratio, normalisedstat mean
Script
admin
#1. Quarterly revenue in kEUR over 3 years;
json load "rx_p4h_q" "[120, 95, 88, 142, 131, 104, 96, 155, 140, 112, 101, 168]";
#2. Centred moving average 2x4 and ratio to the trend;
json load "rx_p4h_ratio" "{\"0\": [], \"1\": [], \"2\": [], \"3\": []}";
for (-> "[t]" 2) (< [t] 10) (++ "[t]") {
	-> "[cma]" (/ (+ (/ (json select "rx_p4h_q" (concat "/[" (- [t] 2) "]")) 2) (json select "rx_p4h_q" (concat "/[" (- [t] 1) "]")) (json select "rx_p4h_q" (concat "/[" [t] "]")) (json select "rx_p4h_q" (concat "/[" (+ [t] 1) "]")) (/ (json select "rx_p4h_q" (concat "/[" (+ [t] 2) "]")) 2)) 4);
	json iarray "rx_p4h_ratio" (concat "/" (math mod [t] 4)) (/ (json select "rx_p4h_q" (concat "/[" [t] "]")) [cma]) NUM;
};
#3. Seasonal index of each quarter, normalised to a mean of 1;
-> "[idx]" "";
-> "[s]" (+ (stat mean (json select "rx_p4h_ratio" "/0")) (stat mean (json select "rx_p4h_ratio" "/1")) (stat mean (json select "rx_p4h_ratio" "/2")) (stat mean (json select "rx_p4h_ratio" "/3")));
for (-> "[k]" 0) (< [k] 4) (++ "[k]") {
	-> "[idx]" (concat [idx] " Q" (+ [k] 1) "=" (math round (/ (* 4 (stat mean (json select "rx_p4h_ratio" (concat "/" [k])))) [s]) 3));
};
concat "seasonal indices:" [idx]
Result
mentdb
seasonal indices: Q1=1.112 Q2=0.869 Q3=0.782 Q4=1.237
Go further
  • Divide each quarter by its index to get the deseasonalised series, then fit pa rl load_from_json on it.
  • Multiply the trend forecast by the index of the quarter to get the final forecast.
  • Check the stability of the indices year by year with stat stddev.

Also in AI & prediction

ML
Machine learning engineer
Density clustering, graph search and data quality gates, in memory on the server.
10 recipes
DL
Deep learning engineer
Text classifiers, feed-forward networks on CSV data and image networks.
10 recipes
CB
Conversational bot designer
Bots, intents, users, rights and releases between servers.
10 recipes