Function reference

The complete surface: 39 functions, deliberately small. Each links to its own page with the signature, when to use it, examples, gotchas — and for the context and time functions, the SQL it compiles into. If you’d rather start from the business question than the function name, start with the recipes.

Aggregations

Function	Use it for
`Sum(column)`	totals — the additive base everything builds on
`Count(column)`	rows with a value
`CountDistinct(column)`	unique customers, orders, SKUs
`Avg(column)`	the mean at row grain
`Min(column)` / `Max(column)`	first/latest dates, floors & ceilings
`Median(column)`	typical values, outlier-proof (dialect-gated)
`Percentile(column, p)`	P90/P95 SLAs, distribution cutoffs (dialect-gated)
`Stdev(column)` / `Var(column)`	volatility & consistency

Iterators (row expressions)

Function	Use it for
`SumX(table, rowExpr)`	weighted totals — `Qty × Price` before summing
`AvgX(table, rowExpr)` / `MinX` / `MaxX`	average / extremes of a computed line value
`CountRows(table)`	plain row counts at the query’s grain

Context

Function	Use it for
`Calculate(expr, mod…)`	filtered variants, overrides, comparisons — the context tool
`RemoveFilters(dims…)`	%-of-total denominators, grand totals
`KeepFilters(pred)`	spelling additive intent explicitly (alias)
`Fixed(dims…, expr [, IgnoreFilters])`	per-entity values at a pinned grain (LOD)
`Include(dims…, expr)`	view grain plus dims — per-entity averages that follow the view
`Exclude(dims…, expr)`	view grain minus dims — %-of-subtotal denominators

Time intelligence

Function	Use it for
`DateShift(col, n, unit)`	same period last year/quarter — the YoY tool
`YTD(expr, col)` / `QTD` / `MTD`	to-date accumulation & pacing
`RollingSum(expr, n, grain)`	trailing 12 months, moving windows
`LastNonEmpty(expr, col)`	current balances, stock on hand, headcount
`PeriodEndSnapshot(expr, col, grain)`	month-end balance trend lines

Logic & variables

Function	Use it for
`Iif(cond, then, else)`	two-branch flags
`Case(cond, r, … [, default])`	tiers & bands — first true wins
`Switch(expr, v, r, … [, default])`	mapping specific values to labels
`Let(name, expr, body)`	name a sub-expression once, compute it once

Scalars

Function	Use it for
`Divide(a, b [, fallback])`	every ratio — safe on zero by default
`Round(v, digits)` / `Abs(v)`	rounded values that feed more math; deltas by magnitude
`Upper(s)` / `Lower(s)`	label case normalization
`Coalesce(a, b, …)`	show 0 instead of blank
`ToText(v)`	numbers/dates → strings for `&` labels

Reserved

UseRelationship(fromCol, toCol) — evaluate over an alternate join path (role-playing dates: order vs ship). Signature frozen; builds when the first model needs it.

Recipes — start from the business question.
The SquareX guide
Syntax & grammar
Diagnostics