Skip to main content
DocumentationLibraryReference
On this page

Kater SQL Functions Field declarations

CUSTOM_CALCULATION

Declares a custom calculation: a value worked out over the rows of the query's result, such as a rank or a running total.

Family: Field declarations

It reads the rows the query returns, after it groups them.

Syntax 

CUSTOM_CALCULATION('name', formula) AS "Label"

Arguments

ArgumentDescription
nameThe field’s name, in quotes. No other field in this SELECT, or in the models it reads, can have it, in any letter case.
formulaA window over the result rows, such as RANK() OVER (ORDER BY EVAL(orders.sum__quantity) DESC), an EXCEL formula, or an expression over another custom calculation.

Returns

A value for each row of the result.

Usage notes

  • Write it only in a query’s final SELECT, as a whole column. Queries, tests and the Sandbox have them. A shared model does not.
  • Its value depends on the other rows of the result. A reader can select it, filter by it and group by it, but cannot EVAL it.
  • A SUM or AVG of a custom calculation combines finished results. It is not worked out again.
  • A formula that neither runs a window nor reads a custom calculation is published as a dimension.

Example

This Sandbox query reads the sample shop’s orders. The table shows the rows that Kater gave.

SELECT
  orders.channel AS "Channel",
  EVAL(orders.sum__quantity) AS "Units",
  CUSTOM_CALCULATION('units_rank', RANK() OVER (ORDER BY EVAL(orders.sum__quantity) DESC)) AS "Units Rank"
FROM kater_dataset.orders AS orders
GROUP BY
  orders.channel
ORDER BY
  1
ChannelUnitsUnits Rank
email2,3831
paid2,2923
store2,3302
web2,1494

See also