Revenue and profit forecast functions
Project revenue, profit and margin forward from your own daily history, with a confidence band and a plain verdict on how reliable the line is.
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.
FORECAST_DASHBOARD is the exception: it has no account id and takes the period first.
FORECAST_REVENUE(accountId, period, [periodEnd])
FORECAST_PROFIT_MARGIN(accountId, period, [periodEnd])
FORECAST_GROWTH_RISK(accountId, period, [periodEnd])
ADVANCED_FORECAST_DASHBOARD(accountId, period, [periodEnd], [horizonDays])
FORECAST_DASHBOARD(
period,
[periodEnd],
[fields],
[locationId],
[productLimit]
)Parameters and arguments
Arguments are written in brackets, separated by commas. An argument in square brackets is optional.
FORECAST_REVENUE
Required: YesaccountId
Required: Yesperiod
Required: NoperiodEndFORECAST_PROFIT_MARGIN
Required: YesaccountId
Required: Yesperiod
Required: NoperiodEndFORECAST_GROWTH_RISK
Required: YesaccountId
Required: Yesperiod
Required: NoperiodEndADVANCED_FORECAST_DASHBOARD
Required: YesaccountId
Required: Yesperiod
Required: NoperiodEnd
Required: NohorizonDaysFORECAST_DASHBOARD
Required: Yesperiod
Required: NoperiodEnd
Required: Nofields
Required: NolocationId
Required: NoproductLimit
Examples
The last 90 days of daily revenue, a fitted trend line, a 30 day projection with a 95 per cent band, and how reliable the fit is.
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_REVENUE | record | Daily revenue history, a fitted trend, a 30 day projection with a 95 per cent band, and fit statistics | FORECAST_REVENUE("ACCOUNT_ID", "ROLLING_90_DAYS") |
FORECAST_PROFIT_MARGIN | record | Daily revenue, gross profit and contribution with their margin trends and a 30 day profit projection | FORECAST_PROFIT_MARGIN("ACCOUNT_ID", "ROLLING_90_DAYS") |
FORECAST_GROWTH_RISK | record | Growth, confidence and risk measures for revenue and margin in three groups | FORECAST_GROWTH_RISK("ACCOUNT_ID", "ROLLING_90_DAYS") |
ADVANCED_FORECAST_DASHBOARD | record | Several forecasting models side by side for revenue and orders, with the model the data supports and diagnostics | ADVANCED_FORECAST_DASHBOARD("ACCOUNT_ID", "ROLLING_90_DAYS", 30) |
FORECAST_DASHBOARD | record | The revenue, profit, demand, seasonality, cash flow and product sections of the forecast tab, computed together | FORECAST_DASHBOARD("ROLLING_90_DAYS", "revenue_forecast") |
Money series (actual, fitted, forecast and the bands) are lists of whole numbers as stored for the account's currency. They are not rescaled to a decimal amount, so divide by the currency's scale before you show them. The statistics (slope, intercept, rsq) are decimals written as text with four places.
FORECAST_REVENUE answers
| Field | Type | Meaning |
|---|---|---|
dates | list of text | The days in the range, oldest first |
actual | list of whole numbers | Revenue for each day |
fitted | list of whole numbers | The straight line fitted to the history, for each day |
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 | The projected revenue for the next 30 days |
upperBand | list of whole numbers | The top of the 95 per cent band |
lowerBand | list of whole numbers | The bottom of the band, never below zero |
slope | text | Revenue added per day by the trend |
intercept | text | Where the trend line starts |
rsq | text | How much of the movement the line explains, 0 to 1 |
confidence | text | HIGH, MEDIUM or LOW, graded from rsq |
revenueGrowthRate | text | The slope as a share of average daily revenue |
steyx | text | The typical miss of the line, which sets the width of the band |
growthForecast | list of whole numbers | A compounding (exponential) projection for comparison with the straight line |
currency | text | The account currency |
FORECAST_PROFIT_MARGIN answers
| Field | Type | Meaning |
|---|---|---|
dates | list of text | The days in the range |
revenue | list of whole numbers | Daily revenue |
grossProfit | list of whole numbers | Daily gross profit |
contribution | list of whole numbers | Daily revenue less cost of goods and payment fees |
marginBpsFitted | list of whole numbers | The fitted gross margin trend, in hundredths of a percentage point |
contribBpsFitted | list of whole numbers | The fitted contribution margin trend, in the same unit |
ewmaMarginBps | list of whole numbers | The smoothed gross margin series |
ewmaContribBps | list of whole numbers | The smoothed contribution margin series |
forecastDates | list of text | +1d to +30d |
forecastProfit | list of whole numbers | Projected daily gross profit |
forecastContribution | list of whole numbers | Projected daily contribution |
profitSlope | text | Gross profit added per day by the trend |
profitIntercept | text | Where the profit trend starts |
marginSlope | text | Change in gross margin per day |
marginIntercept | text | Where the margin trend starts |
contributionSlope | text | Contribution added per day by the trend |
contributionIntercept | text | Where the contribution trend starts |
currency | text | The account currency |
FORECAST_GROWTH_RISK answers three records
| Field | Type | Meaning |
|---|---|---|
growthMetrics | record | revenueSlope, profitSlope, marginSlope, revenueGrowthRate, profitGrowthRate and ewmaRevenueAcceleration (how far a fast average has pulled ahead of a slow one) |
confidenceMetrics | record | revenueRsq, profitRsq, revenueProfitCorrelation, revenueSteyx, profitSteyx and forecastConfidence (HIGH, MEDIUM or LOW) |
riskMetrics | record | revenueVolatility, marginVolatility, marginRiskLevel (HIGH, MEDIUM or LOW) and marginCompressionRisk (true when margin is falling) |
ADVANCED_FORECAST_DASHBOARD answers
| Field | Type | Meaning |
|---|---|---|
revenueForecast | record | linear, robust, quantileP25, quantileP50, quantileP75, arima and bayesian, each a list for the horizon, and selectedModel |
orderCountForecast | record | The order count projection from the order model the data supports |
cashflowForecast | list | Always an empty list. Use FORECAST_CASHFLOW for cash |
modelSelection | record | revenueModel, orderModel, their justifications in words, and confidence |
diagnostics | record | revenueRsq, revenueCv, residualSkewness, revenueSteyx, revenueOrderCorrelation, observationCount, fci and fciLevel |
riskMetrics | record | volatility, downsideRisk and upsidePotential |
forecastDivergence | text | How far the robust and linear projections have drifted apart, as a percentage |
divergenceWarning | text or null | A sentence when recent outliers are distorting the baseline projection |
FORECAST_DASHBOARD answers a record with the sections you asked for
| Field | Type | Meaning |
|---|---|---|
revenue_forecast | record | The FORECAST_REVENUE answer |
profit_forecast | record | The FORECAST_PROFIT_MARGIN answer |
order_forecast | record | The FORECAST_DEMAND answer |
seasonality | record | The FORECAST_SEASONALITY answer |
cashflow_forecast | record | The FORECAST_CASHFLOW answer |
product_demand_forecast | record | The FORECAST_PRODUCT_DEMAND answer |
growth_metrics, confidence_metrics, risk_metrics | records | The measures from FORECAST_GROWTH_RISK. They are always present, empty when not asked for |
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
- With fewer than 3 days of history in the range, the single function answers empty lists and zero statistics instead of an error.
ADVANCED_FORECAST_DASHBOARDneeds at least 5 days, and answers a low confidence default below that. - The projection is always 30 days, except
ADVANCED_FORECAST_DASHBOARD, wherehorizonDayssets it. - The line is a straight line fitted to the days in range. A short or seasonal history gives a wide band: read
confidencefirst. FORECAST_DASHBOARDacceptslocationIdbut computes the sections for the whole account.
Permissions and security
These functions read your daily metrics, cost of goods and payment fees. The caller needs read access to the records named, and nothing is written.
They are governed by the same read access as the tables they read, so no separate grant is needed beyond it.
Best practices
- Use at least 60 days of history, so the fit has something to learn from.
- Show
lowerBandandupperBandbesideforecast: a single line looks more certain than it is. - Ask
FORECAST_DASHBOARDfor named sections only.