Calculations

A calculation is a column computed from a SQL formula. The formula references measures and dimensions by name in brackets and the planner substitutes the right expressions at the right node, so a ratio of two measures from two fact tables lands after both are aggregated, and a window over a count lands outside the GROUP BY.

Declaring one

Inline among the projections with calculation: true, or in the top-level calculations list (where every entry is a calculation and follows the projections):

{"projections": [
  {"field": "category"},
  {"field": "net_paid", "alias": "Net Paid"},
  {"alias": "Bucket", "calculation": true, "sql": "[Net Paid] / 100", "data_type": "decimal"}
]}

{"projections": [{"field": "category"}, {"field": "net_paid", "alias": "Net Paid"}],
 "calculations": [{"alias": "Bucket", "sql": "[Net Paid] / 100"}]}

field and field_type are ignored on a calculation; its kind comes from the formula.

Formula grammar

The formula is SQL text with three kinds of bracket reference:

Whitespace inside brackets is normalized: [ Net Paid ] is [Net Paid]. An [Alias] that matches nothing fails with:

Sql could not find a projection with alias X. If you meant to references a measure or dimension used @m or @d to clarify: [My Measure]@m

Measure or dimension calculation. If the formula calls an aggregate function it is a measure calculation and is computed where the query aggregates; otherwise it is a dimension calculation. The aggregates recognised: sum, count, avg, min, max, median, mode, stddev, stddev_pop, stddev_samp, variance, var_pop, var_samp, array_agg, string_agg, listagg, bool_and, bool_or, every, bit_and, bit_or, count_if, percentile_cont, percentile_disc, corr, covar_pop, covar_samp, approx_distinct, approx_count_distinct.

No bare columns. Any identifier that is not a reserved keyword or a function name is taken as a raw table column and rejected:

Sql columns like x,y are not permitted in calculations.

Everything must go through brackets. String literals in single quotes are fine; double-quoted identifiers count as columns.

Allowed constructs. CASE WHEN ... THEN ... ELSE ... END, nullif, coalesce, cast, extract(... from ...), ||, arithmetic, and window clauses over (partition by ... order by ...). There is no if(); write CASE.

Calc over calc. A calculation may reference another by [Alias]. A cycle fails with Calculation X references itself through Y.

Examples

Each of these is a real formula the planner accepts, with what it does.

1. Ratio of two measures that live on different fact tables. Each is aggregated in its own CTE and the division happens at the merge.

{"alias": "2x WPaid", "sql": "[Net Paid]@m/[Web Net Paid]@m", "data_type": "decimal", "calculation": true}

2. Aggregated ratio: aggregates around measure references make it a measure calculation.

{"alias": "Agg Ratio", "sql": "sum([Web Net Paid]@m)/max([Net Paid]@m)", "calculation": true}

3. Conditional distinct count: how many categories had any sale, in store or on the web.

{"alias": "Actives", "data_type": "integer", "calculation": true,
 "sql": "count(distinct case when [Net Paid]@m > 0 or [Web Net Paid]@m > 0 then [Category]@d else null end)"}

4. A calculation over another calculation, with a window: each category’s actives relative to the first category’s.

{"alias": "Retention Rate", "calculation": true,
 "sql": "[Actives] / nullif(first_value([Actives]) over (order by [Category]@d), 0)"}

5. Dimension calculation producing a string: concatenation of two dimensions. [Category] here resolves by alias to the projected Category column.

{"alias": "Cat Concat Item", "data_type": "string", "calculation": true,
 "sql": "[Category] || ' ' || [Product Name]@d"}

6. Date part as a dimension calculation.

{"alias": "Year", "data_type": "integer", "calculation": true, "sql": "EXTRACT(year FROM [Date]@d)"}

7. Margin percent, guarded against division by zero.

{"alias": "Margin %", "calculation": true, "sql": "([Net Paid]@m - [Cost]@m) / nullif([Net Paid]@m, 0)"}

The SQL

A calculation standing on a measure, inside a blend of two facts. [Net Paid] references the projection by alias:

{"spec": {
  "projections": [
    {"field": "category", "alias": "Category"},
    {"field": "net_paid", "alias": "Net Paid"},
    {"alias": "Bucket", "calculation": true, "sql": "[Net Paid] / 100", "data_type": "decimal"},
    {"field": "ws_net_paid", "alias": "Web Net Paid"}
  ],
  "filters": [{"field": "category", "predicate": "in_list", "value": "men ,children, \"books,com\""}]
}}

WITH agc51c5a013f468165df0d33fc12150511 AS (
SELECT
	T1."i_category" AS "dim30d09b7",
	sum(T0."ss_net_paid") AS "msr8a51bb0"
FROM
	store_sales T0
	JOIN item T1
		ON T0.ss_item_sk = T1.i_item_sk
WHERE
	LOWER(T1."i_category") IN ('men', 'children', '"books,com"')
GROUP BY
	T1."i_category"
), ag89dc4ad5879fa4552696fe3de5b10435 AS (
SELECT
	T1."i_category" AS "dim30d09b7",
	sum(T0."ws_net_paid") AS "msr60b3792"
FROM
	web_sales T0
	JOIN item T1
		ON T0.ws_item_sk = T1.i_item_sk
WHERE
	LOWER(T1."i_category") IN ('men', 'children', '"books,com"')
GROUP BY
	T1."i_category"
)
SELECT
	COALESCE(A1.dim30d09b7, A0.dim30d09b7) AS "Category",
	A1.msr8a51bb0 AS "Net Paid",
	A1.msr8a51bb0 / 100 AS "Bucket",
	A0.msr60b3792 AS "Web Net Paid"
FROM
	ag89dc4ad5879fa4552696fe3de5b10435 A0
	FULL OUTER JOIN agc51c5a013f468165df0d33fc12150511 A1
		ON A1.dim30d09b7 = A0.dim30d09b7

Bucket is computed in the final SELECT over the already-aggregated msr8a51bb0, not inside the store sales CTE. A formula that mixed [Net Paid]@m and [Web Net Paid]@m would land in the same place, with both sides available.

A calculation over a calculation, with a window. Projections: category, Actives = count(distinct [Category]@d) (integer) and Retention Rate = [Actives] / nullif(first_value([Actives]) over (order by [Category]@d), 0):

SELECT
	A0.dim30d09b7 AS "Category",
	count(distinct A0.dim30d09b7) AS "Actives",
	count(distinct A0.dim30d09b7) / nullif(first_value(count(distinct A0.dim30d09b7)) over (order by A0.dim30d09b7), 0) AS "Retention Rate"
FROM
	(
SELECT
	T0."i_category" AS "dim30d09b7"
FROM
	web_sales_sum T0
GROUP BY
	T0."i_category"
) A0
GROUP BY
	A0.dim30d09b7

[Actives] is expanded to its formula wherever it is used, and the first_value(...) over (...) window stays out of the GROUP BY. Which table answers the category list (web_sales_sum in that fixture) is the resolver’s choice; see Universe formation and Cost optimization.

Decorators on calculations

A calculation accepts decorators like a projection; they are validated without a field, so the kind rules apply to the calculation’s own kind. A measure calculation can carry window, contribute or temporalize:

{"alias": "Margin %", "calculation": true,
 "sql": "([Net Paid]@m - [Cost]@m) / nullif([Net Paid]@m, 0)",
 "decorators": [{"type": "window", "mode": "moving", "function": "avg", "size": 3}]}

Complex measures

How the pieces combine. Each step is a valid formula; the SQL shape is described rather than shown.

Margin percent. Two measures from the same fact, a guarded division. Both are aggregated in the fact’s node and the division happens on the sums, so it is a weighted margin, not an average of row margins.

{"alias": "Margin %", "calculation": true, "sql": "([Net Paid]@m - [Cost]@m) / nullif([Net Paid]@m, 0)"}

Conditional distinct count. Count members that meet a measure condition. count(distinct ...) makes it a measure calculation; the CASE reads measures and a dimension together, so the planner evaluates it where both are in scope.

{"alias": "Buying Customers", "data_type": "integer", "calculation": true,
 "sql": "count(distinct case when [Net Paid]@m > 0 then [Customer Id]@d end)"}

Share of another measure across facts. Web revenue as a fraction of store revenue. The two measures come from web_sales and store_sales; each is summed in its own aggregation CTE at the projected grain, the CTEs are joined on the shared dimensions, and the ratio is computed in the final pass (the same shape as Bucket above, with a measure on each side).

{"alias": "Web Share", "calculation": true, "sql": "[Web Net Paid]@m / nullif([Net Paid]@m + [Web Net Paid]@m, 0)"}

Ratio referencing a window. First declare the windowed quantity as its own calculation, then reference it by alias. The window stays in the final SELECT, outside the GROUP BY, like Retention Rate above.

[
  {"alias": "Running Web", "calculation": true,
   "sql": "sum([Web Net Paid]@m) over (order by [Month(Date)])"},
  {"alias": "Share of Running", "calculation": true, "sql": "[Web Net Paid]@m / nullif([Running Web], 0)"}
]

[Month(Date)] is the derived alias of a month-truncated date projection; give the projection an explicit alias if you prefer [Month]. The built-in window decorator covers the plain running and moving cases without a formula.

Next steps

• Projections and decorators for what a decorator can do before you reach for a formula
• Shorthand expressions: Margin % := ([Net Paid]@m - [Cost]@m) / nullif([Net Paid]@m, 0)
• Errors and corrections