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

    values

    Required: Yes
  • horizon

    Required: Yes
  • delta

    Required: No
  • QUANTILE_FORECAST

    values

    Required: Yes
  • horizon

    Required: Yes
  • quantile

    Required: Yes
  • POISSON_FORECAST

    values

    Required: Yes
  • horizon

    Required: Yes
  • NEGATIVE_BINOMIAL_FORECAST

    values

    Required: Yes
  • horizon

    Required: Yes
  • ARIMA_FORECAST

    values

    Required: Yes
  • horizon

    Required: Yes
  • p

    Required: No
  • d

    Required: No
  • q

    Required: No
  • seasonal_period

    Required: No
  • BAYESIAN_FORECAST

    values

    Required: Yes
  • horizon

    Required: Yes
  • prior_mean

    Required: No
  • prior_variance

    Required: No

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.

FunctionReturnsBehaviourForm
ROBUST_FORECASTlist of decimalsThe projected value for each period ahead, from a line that gives less weight to extreme valuesROBUST_FORECAST([40, 42, 39, 41, 120, 43], 3)
QUANTILE_FORECASTlist of decimalsThe projected value for each period ahead at that quantileQUANTILE_FORECAST([40, 42, 39, 41, 43], 3, 0.9)
POISSON_FORECASTrecordA record with forecast, upper_bound and lower_bound (lists of whole counts, never below 0), and lambda, trend_slope and dispersion as textPOISSON_FORECAST([12, 15, 11, 14], 3)
NEGATIVE_BINOMIAL_FORECASTrecordA 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 textNEGATIVE_BINOMIAL_FORECAST([12, 15, 11, 14], 3)
ARIMA_FORECASTrecordA 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 forecastARIMA_FORECAST([210, 215, 208, 220, 225, 219, 230], 3, 1, 1, 0, 0)
BAYESIAN_FORECASTrecordA 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_countBAYESIAN_FORECAST([210, 215, 208, 220], 3, 215, 100)
Errors and exceptions
ErrorWhat happenedHow to recover
HTQL-5001The function was given the wrong number of arguments. The message names the function and the countsGive it the arguments listed under Parameters
HTQL-1004 Unsupported functionThe name is not a function this engine runsCheck the name and its spelling against this entry
HTQL-2001The series is not a list of numbers, or a horizon, order or quantile is not a numberPass a list of numbers and whole-number periods
HTQL-2001The series is too short for the model, the horizon is below 1, or the quantile is not above 0 and below 1Give 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_FORECAST needs 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.