Forecasting model functions
Project a series of daily or weekly figures forward.
Syntax
Write the function's name with the series, a list of numbers in time order, and how many periods ahead to project.
ROBUST_FORECAST(values, horizon, [delta])
QUANTILE_FORECAST(values, horizon, quantile)
POISSON_FORECAST(values, horizon)
NEGATIVE_BINOMIAL_FORECAST(values, horizon)
ARIMA_FORECAST(values, horizon, [p], [d], [q], [seasonal_period])
BAYESIAN_FORECAST(values, horizon, [prior_mean], [prior_variance])Parameters and arguments
Arguments are written in brackets, separated by commas. An argument in square brackets is optional.
ROBUST_FORECAST
Required: Yesvalues
Required: Yeshorizon
Required: NodeltaQUANTILE_FORECAST
Required: Yesvalues
Required: Yeshorizon
Required: YesquantilePOISSON_FORECAST
Required: Yesvalues
Required: YeshorizonNEGATIVE_BINOMIAL_FORECAST
Required: Yesvalues
Required: YeshorizonARIMA_FORECAST
Required: Yesvalues
Required: Yeshorizon
Required: Nop
Required: Nod
Required: Noq
Required: Noseasonal_periodBAYESIAN_FORECAST
Required: Yesvalues
Required: Yeshorizon
Required: Noprior_mean
Required: Noprior_variance
Examples
Ten days of orders with one promotion spike: the next three days, a line fitted without the spike pulling it, and a cautious level for stock cover.
Return type and behaviour
Each function returns one value. The table shows its type, what it does and one way to write it; the examples use the functions in realistic statements.
| Function | Returns | Behaviour | Form |
|---|---|---|---|
ROBUST_FORECAST | list of decimals | The projected value for each period ahead, from a line that gives less weight to extreme values | ROBUST_FORECAST([40, 42, 39, 41, 120, 43], 3) |
QUANTILE_FORECAST | list of decimals | The projected value for each period ahead at that quantile | QUANTILE_FORECAST([40, 42, 39, 41, 43], 3, 0.9) |
POISSON_FORECAST | record | A record with forecast, upper_bound and lower_bound (lists of whole counts, never below 0), and lambda, trend_slope and dispersion as text | POISSON_FORECAST([12, 15, 11, 14], 3) |
NEGATIVE_BINOMIAL_FORECAST | record | A record with forecast, upper_bound and lower_bound (lists of whole counts, never below 0), and mu, r_param, p_param, dispersion and trend_slope as text | NEGATIVE_BINOMIAL_FORECAST([12, 15, 11, 14], 3) |
ARIMA_FORECAST | record | A record with forecast, upper_bound, lower_bound (lists), ar_coefficients (a list), and residual_sigma and model_order as text. With a seasonal_period the repeat is removed before the model is fitted and added back to the forecast | ARIMA_FORECAST([210, 215, 208, 220, 225, 219, 230], 3, 1, 1, 0, 0) |
BAYESIAN_FORECAST | record | A record with forecast, upper_bound and lower_bound (lists), the posterior and prior figures (posterior_mean, posterior_variance, prior_mean_used, prior_variance_used, sample_mean, sample_variance, prior_weight) as text, trend_slope and observation_count | BAYESIAN_FORECAST([210, 215, 208, 220], 3, 215, 100) |
Errors and exceptions
| Error | What happened | How to recover |
|---|---|---|
HTQL-5001 | The function was given the wrong number of arguments. The message names the function and the counts | Give it the arguments listed under Parameters |
HTQL-1004 Unsupported function | The name is not a function this engine runs | Check the name and its spelling against this entry |
HTQL-2001 | The series is not a list of numbers, or a horizon, order or quantile is not a number | Pass a list of numbers and whole-number periods |
HTQL-2001 | The series is too short for the model, the horizon is below 1, or the quantile is not above 0 and below 1 | Give more history, a horizon of 1 or more and a quantile between 0 and 1 |
Limitations
- The series is a list of numbers in time order, one value per period. The forecast is for the same period length.
- Each model refuses a series shorter than its minimum, and a horizon below 1.
ARIMA_FORECASTneeds at least p + d + 3 values. - The band in each record is a 95 per cent range around the forecast and widens the further ahead it goes.
- A forecast is a projection of the pattern in the series. It does not know about a planned promotion or a closure.
Permissions and security
These functions read nothing from your tables and write nothing.
When a function is applied to a column or a variable, the statement that reads the data needs read access, as its own entry says.
Best practices
- Use ROBUST_FORECAST when a series has the odd spike or outage, and a plain TREND when it does not.
- Use QUANTILE_FORECAST with a high quantile for stock cover and a low one for a revenue floor.
- Use POISSON_FORECAST or NEGATIVE_BINOMIAL_FORECAST for orders, tickets and returns, which are counts.