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

    period

    Required: Yes
  • periodEnd

    Required: No
  • priceChange

    Required: Yes
  • locationId

    Required: No
  • PLANNING_DISCOUNT_SIMULATOR

    period

    Required: Yes
  • periodEnd

    Required: No
  • discount

    Required: Yes
  • demandLift

    Required: Yes
  • durationDays

    Required: Yes
  • scope

    Required: Yes
  • scopeFilter

    Required: Yes
  • locationId

    Required: No
  • DECISION_SCORE

    accountId

    Required: Yes
  • period

    Required: Yes
  • periodEnd

    Required: No
  • SIMULATE_REPLAY_PROFIT_GUARD

    accountId

    Required: Yes
  • period

    Required: Yes
  • periodEnd

    Required: No
  • overrides

    Required: Yes

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.

FunctionReturnsBehaviourForm
PLANNING_PRICING_SIMULATORrecordRevenue, margin and contribution if prices changed, using the account's own price sensitivityPLANNING_PRICING_SIMULATOR("ROLLING_30_DAYS", -500)
PLANNING_DISCOUNT_SIMULATORrecordWhat a discount does to revenue, margin, stock and your own discount rulesPLANNING_DISCOUNT_SIMULATOR("ROLLING_90_DAYS", 2000, NULL, 14, "STOREWIDE", "{}")
DECISION_SCORErecordRecommended pricing, stock, promotion, supplier and macro actions for the period, with scoresDECISION_SCORE("ACCOUNT_ID", "ROLLING_90_DAYS")
SIMULATE_REPLAY_PROFIT_GUARDrecordA period's results worked out again with other rates and limits, on the same recorded factsSIMULATE_REPLAY_PROFIT_GUARD("ACCOUNT_ID", "ROLLING_90_DAYS", '{"taxRateBps": 2500}')

PLANNING_PRICING_SIMULATOR answers

FieldTypeMeaning
baselinerecordrevenue, cogs, grossMargin, contribution and orderCount for the last 30 days
simulatedrecordrevenue, cogs, grossMargin and contribution after the price and demand change
deltarecordrevenue, contribution and grossMargin change
priceChange, demandChangedecimalThe price change you gave and the demand change it causes
elasticitydecimalHow strongly demand reacts to price. More negative means more sensitive
elasticityMethodtextREVENUE_WEIGHTED_SKU, ARC_ELASTICITY or DEFAULT_BENCHMARK
elasticityDataPointswhole numberHow many observations the sensitivity rests on
pricingPowerdecimalA measure of how much price headroom the account has
hasPriceRisktrue or falseWhether the price move carries a risk of losing more than it gains

PLANNING_DISCOUNT_SIMULATOR answers (the main fields)

FieldTypeMeaning
verdicttextPROFITABLE_DISCOUNT, JUSTIFIABLE_LOSS_LEADER, INVENTORY_RECOVERY, MARKETING_DISCOUNT_STACKING, MARGIN_TRADEOFF_FOR_VOLUME, PURE_MARGIN_DESTRUCTION or BLOCKED_BY_GOVERNANCE
outcomerecordcode, summary, recommendations and requiresOverride
scope, periodStart, periodEnd, durationDays, discount, demandLiftas givenThe inputs used, with the demand lift as resolved
baseline, simulated, deltarecordsRevenue, cost, margin and contribution before and after, with the cost of the discount in simulated
promoRoipercent numberReturn on the discount
scopeMetricsrecordSKUs, units and orders in scope, and the stock at risk
basketAttach, marketingOverlay, inventoryRecoveryrecordsExtra profit from items bought with the discounted ones, overlap with marketing spend, and write off cost avoided
perSkurecordhealthy and unhealthy SKUs with their margins, and the counts
governancerecordThe limits applied from your discount settings and whether any is exceeded
crossPolicyStackrecordHow the discount stacks with your other active policies
budgetRunwayrecordThe daily cost of the discount and whether a budget runs out before the end
leakageFlags, appliedBaselineFilters, assumptions, trace, aiSummarylists and recordsSupporting 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

FieldTypeMeaning
decisionScore, confidenceScorepercent numberOverall scores across the five groups
pricingrecordactions (variantId, action, reason, margin, and elasticity or priorMargin), confidence and score
inventoryrecordactions (variantId, action, reason, turns), confidence and score
promotionrecordactions (variantId, action, reason, roi), confidence and score
vendorrecordalerts (vendor, action, reason, share), confidence and score
macrorecordalerts (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
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
  • PLANNING_PRICING_SIMULATOR looks at the last 30 days of orders and ignores the period you pass.
  • Percent arguments are whole numbers: 12.5 is cut to 12. Write 12.5 per cent as 1250.
  • With no order history the simulators answer {error, message} instead of figures.
  • DECISION_SCORE uses 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 hasPriceRisk and elasticityMethod together: a benchmark sensitivity is a guess, a measured one is evidence.
  • Test a discount with the same scope you plan to run.
  • Treat DECISION_SCORE actions as a shortlist to review, not instructions.