Worked examples

These are the measures the SquareX engine itself is validated against — every Phase-1 function appears in at least one. Read them top to bottom and you’ve seen the whole language in action.

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).

Dialect awareness

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

Unsupported combinations fail loudly with SQX010 instead of computing something subtly different.

Related

• The SquareX guide — the concepts behind these.
• Function reference