Demand forecast functions

Know how much customers will order and which products will sell, before you buy stock or staff a shift.

Syntax

Write the function's name and its arguments. The period is written one of three ways: a date window name such as ROLLING_90_DAYS (the date window functions), a start date and an end date such as "2026-01-01", "2026-03-31", or an accounting period id.

POLICY_PROMOTION_FORECAST takes one JSON text instead.

FORECAST_DEMAND(accountId, period, [periodEnd])
FORECAST_PRODUCT_DEMAND(accountId, period, [periodEnd], [limit])
FORECAST_SEASONALITY(accountId, period, [periodEnd])
POLICY_PROMOTION_FORECAST(input)

Parameters and arguments

Arguments are written in brackets, separated by commas. An argument in square brackets is optional.

  • FORECAST_DEMAND

    accountId

    Required: Yes
  • period

    Required: Yes
  • periodEnd

    Required: No
  • FORECAST_PRODUCT_DEMAND

    accountId

    Required: Yes
  • period

    Required: Yes
  • periodEnd

    Required: No
  • limit

    Required: No
  • FORECAST_SEASONALITY

    accountId

    Required: Yes
  • period

    Required: Yes
  • periodEnd

    Required: No
  • POLICY_PROMOTION_FORECAST

    input

    Required: Yes

Examples

The order count trend for the last 90 days, a 30 day projection, and the busiest and quietest days of the week.

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
FORECAST_DEMANDrecordDaily order counts, a fitted trend, a 30 day projection and a weekday patternFORECAST_DEMAND("ACCOUNT_ID", "ROLLING_90_DAYS")
FORECAST_PRODUCT_DEMANDrecordProjected 30 day demand for each product, the declining products, and the products about to run outFORECAST_PRODUCT_DEMAND("ACCOUNT_ID", "ROLLING_90_DAYS", 10)
FORECAST_SEASONALITYrecordMonthly revenue with an index for each month of the year, a year by month heatmap and the peak and trough monthsFORECAST_SEASONALITY("ACCOUNT_ID", "ROLLING_730_DAYS")
POLICY_PROMOTION_FORECASTrecordBaseline demand, scenarios, co-purchase evidence and a role for each product in a planned promotionPOLICY_PROMOTION_FORECAST('{"selectedResources": ["VARIANT_ID"]}')

FORECAST_DEMAND answers

FieldTypeMeaning
dateslist of textThe days in the range
actuallist of whole numbersOrders for each day
fittedlist of whole numbersThe trend line
smaSerieslist of whole numbersA 7 day moving average; the first 6 entries are 0
ewmaSerieslist of whole numbersA smoothed series that weights recent days more
forecastDateslist of text+1d to +30d
forecastlist of whole numbersProjected orders for the next 30 days
slopetextOrders added per day by the trend
demandGrowthRatetextThe slope as a share of average daily orders
seasonalOverlaylist of recordsdayOfWeek (1 is Monday), dayName and index for each weekday. Empty with fewer than 7 days
peakDay, troughDaytextThe busiest and quietest weekday, such as Fri

FORECAST_PRODUCT_DEMAND answers three lists

FieldTypeMeaning
topProductslist of recordsThe products not in decline, largest projected demand first: productId, title, sku, forecastQty30d, slope, ewmaDailyQty, sma7d, trend (GROWING, STABLE or DECLINING) and trendFittedLast
decliningProductslist of recordsThe same fields for products whose trend is falling, steepest first
stockoutRisklist of recordsproductId, title, available, avgDailyDemand and daysToStockout for products that would sell out within 30 days, soonest first

Only products that sold on at least 3 different days appear. Only active products with stock are checked for stockout.

FORECAST_SEASONALITY answers

FieldTypeMeaning
monthlyRevenuelist of recordsmonth (such as 2026-03) and revenue, both text
ewmaSeries, smaSerieslist of whole numbersSmoothed and 3 month moving average series of monthly revenue
seasonalIndiceslist of recordsmonth (1 to 12), monthName and index. 1.00 is an average month; 1.25 is a month 25 per cent above it
heatmaplist of recordsyear and months, the 12 monthly revenues for that year
peakMonth, troughMonthwhole numberThe strongest and weakest month, 1 to 12
seasonalStrengthtextThe gap between the strongest and weakest index. Zero means no seasonal pattern
longTermSlopetextThe trend across the months
trendRsq, trendConfidencetextHow well a straight line fits the months, and HIGH, MEDIUM or LOW
currencytextThe account currency

POLICY_PROMOTION_FORECAST answers

FieldTypeMeaning
policyIdtext or nullThe policy id you passed
windowDays, forecastDayswhole numbersThe lookback and the horizon used
productslist of recordsFor each product: variantId, productId, title, baseline (orders, units, revenue, mean and spread of units per day), scenarios (downside, flat, upside1Sigma, upside2Sigma, each with forecast units, revenue and orders), coPurchases, recommendedRole, recommendationReason, cadenceLabel and a narrative
totalsrecordBaseline and scenario units, orders, revenue and contribution summed over the products
cartTestslist of recordsThe synthetic carts a later simulation should test
advicerecorddecision, headline, message and narrative for the whole promotion

recommendedRole is one of discount_directly, use_as_condition, use_as_action or review_required. The forecast is a planning guide, not an approval: the final answer comes from replaying the synthetic carts through the same discount rules a real checkout uses.

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
Limitations
  • Fewer than 3 days of history gives empty lists and zero statistics, not an error.
  • FORECAST_SEASONALITY gives an index only to the months present in the range until 12 months of data exist, so use a range of a year or more.
  • Demand projections are straight line projections of past sales. They do not know about a price change or a campaign: use POLICY_PROMOTION_FORECAST for that.
Permissions and security

These functions read your daily metrics, order lines, products, product variants and inventories. The caller needs read access to the records named, and nothing is written. POLICY_PROMOTION_FORECAST reads order lines and the product intelligence summary.

They are governed by the same read access as the tables they read, so no separate grant is needed beyond it.

Best practices
  • Check trend and slope together: a product with a high projection and a negative slope is fading.
  • Order stock against stockoutRisk, not against the projection alone.
  • Pass "ROLLING_730_DAYS" to FORECAST_SEASONALITY so each month has two observations.