Worked examples

These are the measures the SquareX engine itself is validated against. Read them top to bottom and you’ve seen the whole language in action. For the same patterns organized by business question — with chart-placement guidance — see the recipes.

The basics

Revenue := Sum(Sales.Amount)

Orders := CountDistinct(Sales.OrderId)

AvgOrderValue := Divide([Revenue], [Orders])

Small measures composed by reference — [Revenue] and [Orders] are reused everywhere below. Divide is blank-safe on zero orders.

Filtered measures

OpenSRCount := Calculate(
    Count(ServiceRequest.Id),
    KeepFilters(ServiceRequest.Status = "Open")
)

Adds a filter that intersects with whatever the dashboard applies — KeepFilters(pred) and a bare predicate are the same thing.

RevenueAllRegions := Calculate([Revenue], RemoveFilters(Sales.Region))

// The override idiom: remove-then-add beats any dashboard Region filter
WestRevenue := Calculate([Revenue], Sales.Region = "West", RemoveFilters(Sales.Region))

Why the second one works regardless of written order: removals apply first.

% of total

PctOfTotalRevenue := Divide(
    [Revenue],
    Calculate([Revenue], RemoveFilters(Product, Customer))
)

The denominator removes Product and Customer from filters and grouping — so each cell divides by the total across both.

Fixed-grain (LOD)

CustomerRevenue := Fixed(Customer.Id, [Revenue])

// The inline :grain shorthand
CustomersPerMonth := Fixed(Date.Date:month, CountDistinct(Sales.CustomerId))

Drop [CustomerRevenue] on a by-region chart with aggregation avg → average revenue per customer, per region.

Time intelligence

RevenueLY := Calculate([Revenue], DateShift(Date.Date, -1, "year"))

RevenueYTD := YTD([Revenue], Date.Date)

Revenue12mRolling := RollingSum([Revenue], 12, "month")

And the ratio-accumulation rule in practice — accumulate parts, then divide:

AOVYTD := Divide(YTD([Revenue]), YTD([Orders]))   // not YTD([AvgOrderValue])

Variables & branching

// Let: name it once, compute it once
MarginPct := Let(rev, [Revenue],
    Divide(rev - Sum(Sales.Cost), rev))

// Case: searched conditions, first true wins
RevenueTier := Case(
    [Revenue] >= 1000000, "Tier 1",
    [Revenue] >= 100000,  "Tier 2",
    "Tier 3")

// Switch: match a value
OrderVolumeBand := Switch([Orders], 0, "None", 1, "Single", "Multi")

Note RevenueTier brackets the measure [Revenue] — branching on a bare column (Switch(Sales.Segment, …)) is row logic and belongs in a calculated field (SQX014).

Iterators, view-relative LOD & snapshots

The Phase-2 surface, exercised the same way:

GrossRevenue := SumX(Sales, Sales.Qty * Sales.Price)

// % within group: denominator follows the view, minus Region
MonthTotal := Exclude(Sales.Region, [Revenue])
PctOfMonth := Divide([Revenue], [MonthTotal])

ClosingStock := LastNonEmpty(Sum(Inventory.OnHand), Inventory.CountedAt)

EOMStock := PeriodEndSnapshot(Sum(Inventory.OnHand), Inventory.CountedAt, "month")

Dialect awareness

// Engine-native but dialect-gated: not on MySQL / SQL Server / BigQuery
MedianOrderValue := Median(Sales.Amount)

P90OrderValue := Percentile(Sales.Amount, 0.9)

Unsupported combinations fail loudly with SQX010 instead of computing something subtly different — the Percentile page has the engine-by-engine support matrix.

Recipes — the same patterns, organized by the business question they answer.
The SquareX guide — the concepts behind these.
Function reference