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
Required: YesaccountId
Required: Yesperiod
Required: NoperiodEndFORECAST_PRODUCT_DEMAND
Required: YesaccountId
Required: Yesperiod
Required: NoperiodEnd
Required: NolimitFORECAST_SEASONALITY
Required: YesaccountId
Required: Yesperiod
Required: NoperiodEndPOLICY_PROMOTION_FORECAST
Required: Yesinput
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.
| Function | Returns | Behaviour | Form |
|---|---|---|---|
FORECAST_DEMAND | record | Daily order counts, a fitted trend, a 30 day projection and a weekday pattern | FORECAST_DEMAND("ACCOUNT_ID", "ROLLING_90_DAYS") |
FORECAST_PRODUCT_DEMAND | record | Projected 30 day demand for each product, the declining products, and the products about to run out | FORECAST_PRODUCT_DEMAND("ACCOUNT_ID", "ROLLING_90_DAYS", 10) |
FORECAST_SEASONALITY | record | Monthly revenue with an index for each month of the year, a year by month heatmap and the peak and trough months | FORECAST_SEASONALITY("ACCOUNT_ID", "ROLLING_730_DAYS") |
POLICY_PROMOTION_FORECAST | record | Baseline demand, scenarios, co-purchase evidence and a role for each product in a planned promotion | POLICY_PROMOTION_FORECAST('{"selectedResources": ["VARIANT_ID"]}') |
FORECAST_DEMAND answers
| Field | Type | Meaning |
|---|---|---|
dates | list of text | The days in the range |
actual | list of whole numbers | Orders for each day |
fitted | list of whole numbers | The trend line |
smaSeries | list of whole numbers | A 7 day moving average; the first 6 entries are 0 |
ewmaSeries | list of whole numbers | A smoothed series that weights recent days more |
forecastDates | list of text | +1d to +30d |
forecast | list of whole numbers | Projected orders for the next 30 days |
slope | text | Orders added per day by the trend |
demandGrowthRate | text | The slope as a share of average daily orders |
seasonalOverlay | list of records | dayOfWeek (1 is Monday), dayName and index for each weekday. Empty with fewer than 7 days |
peakDay, troughDay | text | The busiest and quietest weekday, such as Fri |
FORECAST_PRODUCT_DEMAND answers three lists
| Field | Type | Meaning |
|---|---|---|
topProducts | list of records | The products not in decline, largest projected demand first: productId, title, sku, forecastQty30d, slope, ewmaDailyQty, sma7d, trend (GROWING, STABLE or DECLINING) and trendFittedLast |
decliningProducts | list of records | The same fields for products whose trend is falling, steepest first |
stockoutRisk | list of records | productId, 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
| Field | Type | Meaning |
|---|---|---|
monthlyRevenue | list of records | month (such as 2026-03) and revenue, both text |
ewmaSeries, smaSeries | list of whole numbers | Smoothed and 3 month moving average series of monthly revenue |
seasonalIndices | list of records | month (1 to 12), monthName and index. 1.00 is an average month; 1.25 is a month 25 per cent above it |
heatmap | list of records | year and months, the 12 monthly revenues for that year |
peakMonth, troughMonth | whole number | The strongest and weakest month, 1 to 12 |
seasonalStrength | text | The gap between the strongest and weakest index. Zero means no seasonal pattern |
longTermSlope | text | The trend across the months |
trendRsq, trendConfidence | text | How well a straight line fits the months, and HIGH, MEDIUM or LOW |
currency | text | The account currency |
POLICY_PROMOTION_FORECAST answers
| Field | Type | Meaning |
|---|---|---|
policyId | text or null | The policy id you passed |
windowDays, forecastDays | whole numbers | The lookback and the horizon used |
products | list of records | For 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 |
totals | record | Baseline and scenario units, orders, revenue and contribution summed over the products |
cartTests | list of records | The synthetic carts a later simulation should test |
advice | record | decision, 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
| 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 |
Limitations
- Fewer than 3 days of history gives empty lists and zero statistics, not an error.
FORECAST_SEASONALITYgives 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_FORECASTfor 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
trendandslopetogether: 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"toFORECAST_SEASONALITYso each month has two observations.