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

    accountId

    Required: Yes
  • period

    Required: Yes
  • periodEnd

    Required: No
  • FORECAST_PROFIT_MARGIN

    accountId

    Required: Yes
  • period

    Required: Yes
  • periodEnd

    Required: No
  • FORECAST_GROWTH_RISK

    accountId

    Required: Yes
  • period

    Required: Yes
  • periodEnd

    Required: No
  • ADVANCED_FORECAST_DASHBOARD

    accountId

    Required: Yes
  • period

    Required: Yes
  • periodEnd

    Required: No
  • horizonDays

    Required: No
  • FORECAST_DASHBOARD

    period

    Required: Yes
  • periodEnd

    Required: No
  • fields

    Required: No
  • locationId

    Required: No
  • productLimit

    Required: No

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.

FunctionReturnsBehaviourForm
FORECAST_REVENUErecordDaily revenue history, a fitted trend, a 30 day projection with a 95 per cent band, and fit statisticsFORECAST_REVENUE("ACCOUNT_ID", "ROLLING_90_DAYS")
FORECAST_PROFIT_MARGINrecordDaily revenue, gross profit and contribution with their margin trends and a 30 day profit projectionFORECAST_PROFIT_MARGIN("ACCOUNT_ID", "ROLLING_90_DAYS")
FORECAST_GROWTH_RISKrecordGrowth, confidence and risk measures for revenue and margin in three groupsFORECAST_GROWTH_RISK("ACCOUNT_ID", "ROLLING_90_DAYS")
ADVANCED_FORECAST_DASHBOARDrecordSeveral forecasting models side by side for revenue and orders, with the model the data supports and diagnosticsADVANCED_FORECAST_DASHBOARD("ACCOUNT_ID", "ROLLING_90_DAYS", 30)
FORECAST_DASHBOARDrecordThe revenue, profit, demand, seasonality, cash flow and product sections of the forecast tab, computed togetherFORECAST_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

FieldTypeMeaning
dateslist of textThe days in the range, oldest first
actuallist of whole numbersRevenue for each day
fittedlist of whole numbersThe straight line fitted to the history, for each day
ewmaSerieslist of whole numbersA smoothed series that weights recent days more
forecastDateslist of text+1d to +30d
forecastlist of whole numbersThe projected revenue for the next 30 days
upperBandlist of whole numbersThe top of the 95 per cent band
lowerBandlist of whole numbersThe bottom of the band, never below zero
slopetextRevenue added per day by the trend
intercepttextWhere the trend line starts
rsqtextHow much of the movement the line explains, 0 to 1
confidencetextHIGH, MEDIUM or LOW, graded from rsq
revenueGrowthRatetextThe slope as a share of average daily revenue
steyxtextThe typical miss of the line, which sets the width of the band
growthForecastlist of whole numbersA compounding (exponential) projection for comparison with the straight line
currencytextThe account currency

FORECAST_PROFIT_MARGIN answers

FieldTypeMeaning
dateslist of textThe days in the range
revenuelist of whole numbersDaily revenue
grossProfitlist of whole numbersDaily gross profit
contributionlist of whole numbersDaily revenue less cost of goods and payment fees
marginBpsFittedlist of whole numbersThe fitted gross margin trend, in hundredths of a percentage point
contribBpsFittedlist of whole numbersThe fitted contribution margin trend, in the same unit
ewmaMarginBpslist of whole numbersThe smoothed gross margin series
ewmaContribBpslist of whole numbersThe smoothed contribution margin series
forecastDateslist of text+1d to +30d
forecastProfitlist of whole numbersProjected daily gross profit
forecastContributionlist of whole numbersProjected daily contribution
profitSlopetextGross profit added per day by the trend
profitIntercepttextWhere the profit trend starts
marginSlopetextChange in gross margin per day
marginIntercepttextWhere the margin trend starts
contributionSlopetextContribution added per day by the trend
contributionIntercepttextWhere the contribution trend starts
currencytextThe account currency

FORECAST_GROWTH_RISK answers three records

FieldTypeMeaning
growthMetricsrecordrevenueSlope, profitSlope, marginSlope, revenueGrowthRate, profitGrowthRate and ewmaRevenueAcceleration (how far a fast average has pulled ahead of a slow one)
confidenceMetricsrecordrevenueRsq, profitRsq, revenueProfitCorrelation, revenueSteyx, profitSteyx and forecastConfidence (HIGH, MEDIUM or LOW)
riskMetricsrecordrevenueVolatility, marginVolatility, marginRiskLevel (HIGH, MEDIUM or LOW) and marginCompressionRisk (true when margin is falling)

ADVANCED_FORECAST_DASHBOARD answers

FieldTypeMeaning
revenueForecastrecordlinear, robust, quantileP25, quantileP50, quantileP75, arima and bayesian, each a list for the horizon, and selectedModel
orderCountForecastrecordThe order count projection from the order model the data supports
cashflowForecastlistAlways an empty list. Use FORECAST_CASHFLOW for cash
modelSelectionrecordrevenueModel, orderModel, their justifications in words, and confidence
diagnosticsrecordrevenueRsq, revenueCv, residualSkewness, revenueSteyx, revenueOrderCorrelation, observationCount, fci and fciLevel
riskMetricsrecordvolatility, downsideRisk and upsidePotential
forecastDivergencetextHow far the robust and linear projections have drifted apart, as a percentage
divergenceWarningtext or nullA sentence when recent outliers are distorting the baseline projection

FORECAST_DASHBOARD answers a record with the sections you asked for

FieldTypeMeaning
revenue_forecastrecordThe FORECAST_REVENUE answer
profit_forecastrecordThe FORECAST_PROFIT_MARGIN answer
order_forecastrecordThe FORECAST_DEMAND answer
seasonalityrecordThe FORECAST_SEASONALITY answer
cashflow_forecastrecordThe FORECAST_CASHFLOW answer
product_demand_forecastrecordThe FORECAST_PRODUCT_DEMAND answer
growth_metrics, confidence_metrics, risk_metricsrecordsThe measures from FORECAST_GROWTH_RISK. They are always present, empty when not asked for
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
  • 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_DASHBOARD needs at least 5 days, and answers a low confidence default below that.
  • The projection is always 30 days, except ADVANCED_FORECAST_DASHBOARD, where horizonDays sets it.
  • The line is a straight line fitted to the days in range. A short or seasonal history gives a wide band: read confidence first.
  • FORECAST_DASHBOARD accepts locationId but 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 lowerBand and upperBand beside forecast: a single line looks more certain than it is.
  • Ask FORECAST_DASHBOARD for named sections only.