Sensitivity simulation functions
Change one assumption and see what moves.
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.
Percentages in these arguments are whole numbers in hundredths of a percentage point: 100 is 1 per cent, 2000 is 20 per cent, and a negative value lowers the figure.
PLANNING_PRICING_SIMULATOR(period, [periodEnd], priceChange, [locationId])
PLANNING_DISCOUNT_SIMULATOR(
period,
[periodEnd],
discount,
demandLift,
durationDays,
scope,
scopeFilter,
[locationId]
)
DECISION_SCORE(accountId, period, [periodEnd])
SIMULATE_REPLAY_PROFIT_GUARD(accountId, period, [periodEnd], overrides)Parameters and arguments
Arguments are written in brackets, separated by commas. An argument in square brackets is optional.
PLANNING_PRICING_SIMULATOR
Required: Yesperiod
Required: NoperiodEnd
Required: YespriceChange
Required: NolocationIdPLANNING_DISCOUNT_SIMULATOR
Required: Yesperiod
Required: NoperiodEnd
Required: Yesdiscount
Required: YesdemandLift
Required: YesdurationDays
Required: Yesscope
Required: YesscopeFilter
Required: NolocationIdDECISION_SCORE
Required: YesaccountId
Required: Yesperiod
Required: NoperiodEndSIMULATE_REPLAY_PROFIT_GUARD
Required: YesaccountId
Required: Yesperiod
Required: NoperiodEnd
Required: Yesoverrides
Examples
Last month's orders repriced at 5 per cent lower, with the demand change the account's own price sensitivity predicts.
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 |
|---|---|---|---|
PLANNING_PRICING_SIMULATOR | record | Revenue, margin and contribution if prices changed, using the account's own price sensitivity | PLANNING_PRICING_SIMULATOR("ROLLING_30_DAYS", -500) |
PLANNING_DISCOUNT_SIMULATOR | record | What a discount does to revenue, margin, stock and your own discount rules | PLANNING_DISCOUNT_SIMULATOR("ROLLING_90_DAYS", 2000, NULL, 14, "STOREWIDE", "{}") |
DECISION_SCORE | record | Recommended pricing, stock, promotion, supplier and macro actions for the period, with scores | DECISION_SCORE("ACCOUNT_ID", "ROLLING_90_DAYS") |
SIMULATE_REPLAY_PROFIT_GUARD | record | A period's results worked out again with other rates and limits, on the same recorded facts | SIMULATE_REPLAY_PROFIT_GUARD("ACCOUNT_ID", "ROLLING_90_DAYS", '{"taxRateBps": 2500}') |
PLANNING_PRICING_SIMULATOR answers
| Field | Type | Meaning |
|---|---|---|
baseline | record | revenue, cogs, grossMargin, contribution and orderCount for the last 30 days |
simulated | record | revenue, cogs, grossMargin and contribution after the price and demand change |
delta | record | revenue, contribution and grossMargin change |
priceChange, demandChange | decimal | The price change you gave and the demand change it causes |
elasticity | decimal | How strongly demand reacts to price. More negative means more sensitive |
elasticityMethod | text | REVENUE_WEIGHTED_SKU, ARC_ELASTICITY or DEFAULT_BENCHMARK |
elasticityDataPoints | whole number | How many observations the sensitivity rests on |
pricingPower | decimal | A measure of how much price headroom the account has |
hasPriceRisk | true or false | Whether the price move carries a risk of losing more than it gains |
PLANNING_DISCOUNT_SIMULATOR answers (the main fields)
| Field | Type | Meaning |
|---|---|---|
verdict | text | PROFITABLE_DISCOUNT, JUSTIFIABLE_LOSS_LEADER, INVENTORY_RECOVERY, MARKETING_DISCOUNT_STACKING, MARGIN_TRADEOFF_FOR_VOLUME, PURE_MARGIN_DESTRUCTION or BLOCKED_BY_GOVERNANCE |
outcome | record | code, summary, recommendations and requiresOverride |
scope, periodStart, periodEnd, durationDays, discount, demandLift | as given | The inputs used, with the demand lift as resolved |
baseline, simulated, delta | records | Revenue, cost, margin and contribution before and after, with the cost of the discount in simulated |
promoRoi | percent number | Return on the discount |
scopeMetrics | record | SKUs, units and orders in scope, and the stock at risk |
basketAttach, marketingOverlay, inventoryRecovery | records | Extra profit from items bought with the discounted ones, overlap with marketing spend, and write off cost avoided |
perSku | record | healthy and unhealthy SKUs with their margins, and the counts |
governance | record | The limits applied from your discount settings and whether any is exceeded |
crossPolicyStack | record | How the discount stacks with your other active policies |
budgetRunway | record | The daily cost of the discount and whether a budget runs out before the end |
leakageFlags, appliedBaselineFilters, assumptions, trace, aiSummary | lists and records | Supporting evidence and the assumptions used |
When nothing in scope sold in the baseline window, the answer is verdict NO_BASELINE_SALES with the policy details and the assumptions, and no margin figures.
DECISION_SCORE answers
| Field | Type | Meaning |
|---|---|---|
decisionScore, confidenceScore | percent number | Overall scores across the five groups |
pricing | record | actions (variantId, action, reason, margin, and elasticity or priorMargin), confidence and score |
inventory | record | actions (variantId, action, reason, turns), confidence and score |
promotion | record | actions (variantId, action, reason, roi), confidence and score |
vendor | record | alerts (vendor, action, reason, share), confidence and score |
macro | record | alerts (indicator, value, alert) for consumer prices, interest rates and income, confidence and score |
When the period has no sales the answer is decisionScore 0, confidenceScore 0 and reason no_sku_data.
SIMULATE_REPLAY_PROFIT_GUARD answers the same record as REPLAY_PROFIT_GUARD, with the overrides applied and the settings version marked as a counterfactual. A warning appears when a discount in the period is above the maximum you set.
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
PLANNING_PRICING_SIMULATORlooks at the last 30 days of orders and ignores the period you pass.- Percent arguments are whole numbers:
12.5is cut to12. Write 12.5 per cent as1250. - With no order history the simulators answer
{error, message}instead of figures. DECISION_SCOREuses fixed limits, set by the platform, to decide when a margin, a stock turn or a vendor share calls for action.
Permissions and security
These functions read your orders, per order profit, order lines, products, inventories, marketing overlays, active promotion policies, settings and macro indicators. 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
- Read
hasPriceRiskandelasticityMethodtogether: a benchmark sensitivity is a guess, a measured one is evidence. - Test a discount with the same scope you plan to run.
- Treat
DECISION_SCOREactions as a shortlist to review, not instructions.