A custom calculation is a value worked out over a query’s result rows: a rank, a running total, the row before, a share of the total, or a comparison with another period. A reader gets the values the query computed, not a recipe it can work out again at its own grouping. Only a query has custom calculations. Tests and the Sandbox have them too, because they compile as queries.

How it works

A custom calculation runs after the query groups its rows, so after WHERE, GROUP BY and HAVING. It reads the finished result rows, one row for each group.

This query of the sample shop ranks the regions by Orders:

1
SELECT
2
  shop.region,
3
  EVAL(shop.count_distinct__order_id),
4
  CUSTOM_CALCULATION(
5
    'rank__count_distinct__order_id',
6
    KATER__RANK(EVAL(shop.count_distinct__order_id), 'desc') OVER ()
7
  ) AS "Orders (Rank)" /* @format number(0) */
8
FROM workspace.shop AS shop
9
GROUP BY
10
  shop.region
11
ORDER BY
12
  3

The rank reads the five result rows. EMEA has the most orders of the five, so its rank is 1. The 64 orders with no region make their own row, and that row ranks last.

The value belongs to this result. Another grouping gives other rows, and other ranks. So a query that reads this one gets the finished values, and it cannot work them out again. A running total by month shows the risk: a reader cannot turn the running totals back into monthly values, and adding them up counts each month more than once. See Read a custom calculation from another query.

Measure or custom calculation

A share of the total can be either. Write it as a custom calculation from the Windows menu, then convert it to a measure when the menu offers that. See Convert to a measure.

Where a calculation can run

Where a window runs decides what kind of field it is.

• A workspace root cannot run a window. Its optional filters apply before its SELECT, so the value would change with each reader’s filters. A chart cannot run a window either: put the window in the chart’s query.
• A window over a measure needs a query. A window over a measure needs a query’s grouping to mean anything. So a shared model runs windows over its dimensions only.
• A window in a CTE that the final SELECT groups by is a grouping key. So it is a dimension, in a query too. Read unchanged in a SELECT that does not group, it stays a custom calculation.

A rank that the query groups by

This query counts how many months each channel had each rank:

1
WITH ranked AS (
2
  SELECT
3
    shop.order_month,
4
    shop.channel,
5
    RANK() OVER (PARTITION BY shop.order_month ORDER BY EVAL(shop.count_distinct__order_id) DESC) AS channel_rank
6
  FROM workspace.shop AS shop
7
  GROUP BY
8
    shop.order_month,
9
    shop.channel
10
)
11
SELECT
12
  DIMENSION('channel_rank', ranked.channel_rank) AS "Channel Rank in Month",
13
  ranked.channel,
14
  MEASURE('months', COUNT(*)) AS "Months"
15
FROM ranked
16
GROUP BY
17
  ranked.channel_rank,
18
  ranked.channel
19
ORDER BY
20
  1,
21
  3 DESC,
22
  2

The first 5 of 16 rows. The CTE ranks the four channels in each month. The final SELECT groups by that rank, so DIMENSION is the right wrapper and the compiler gives no warning. The first places add up to 22 for 21 months, because paid and web tied for first in October 2025. RANK keeps ties.

What makes an output a custom calculation

The formula decides the kind of field, not the wrapper. In a query, an output is a custom calculation when:

• Its SELECT runs a window, named or not.
• It computes a calendar comparison, even when the SQL it compiles to runs no window.
• It is built from a custom calculation in the same SELECT, such as rank__count_distinct__order_id <= 3.
• It selects another query’s custom calculation unchanged, in a SELECT that does not group.
• It is an Excel formula column, CUSTOM_CALCULATION('margin', EXCEL('=[@revenue]-[@cost]')), whatever the formula reads. See Excel formulas.

This query flags the top three regions from the rank in the same SELECT:

1
SELECT
2
  shop.region,
3
  EVAL(shop.count_distinct__order_id),
4
  CUSTOM_CALCULATION(
5
    'rank__count_distinct__order_id',
6
    KATER__RANK(EVAL(shop.count_distinct__order_id), 'desc') OVER ()
7
  ) AS "Orders (Rank)", /* @format number(0) */
8
  CUSTOM_CALCULATION('is_top_3', rank__count_distinct__order_id <= 3) AS "Is Top 3"
9
FROM workspace.shop AS shop
10
GROUP BY
11
  shop.region
12
ORDER BY
13
  3

is_top_3 reads the rank, so it depends on the result rows too. Declared as a DIMENSION, it would get the warning custom_calculation_declared_as_dimension.

Wrappers

CUSTOM_CALCULATION('name', formula) names a custom calculation, as MEASURE and DIMENSION name theirs. When a wrapper disagrees with its formula, the compiler gives a warning and publishes the formula’s kind anyway. Each fix swaps the wrapper and keeps the name and the label.

• A MEASURE over a window is an error in every model. A reader works out a measure one group at a time, so the window would see only that group. In a query, the fix declares it with CUSTOM_CALCULATION. In a shared model, the fix declares it as a DIMENSION.
• CUSTOM_CALCULATION outside a query is an error. The code is custom_calculation_not_allowed, and the fix declares it as a DIMENSION.
• A workspace root runs no window, whatever wraps it. The error is window_not_allowed, and it has no fix.

Filter and sort a custom calculation

• Filter by this number. A number custom calculation offers six fixed conditions under Filters: Is Greater Than, Is At Least, Is Less Than, Is At Most, Is Equal To and Is Not Equal To. Kater writes the right clause for you: QUALIFY for a window, HAVING for a period comparison.
• No Top N on a window. Ranking a window’s value would run a window inside a window. Use Top N on the measure instead. A period comparison runs no window, so it also offers Is In The Top and Is In The Bottom.
• Sort. Sort ascending and Sort descending write the calculation into ORDER BY, as for any field.

Regions whose rank is at most 3

Filters › Is At Most with 3, on Orders (Rank), writes the condition in QUALIFY. QUALIFY runs after the window, so it can read the rank.

1
SELECT
2
  shop.region,
3
  EVAL(shop.count_distinct__order_id),
4
  CUSTOM_CALCULATION(
5
    'rank__count_distinct__order_id',
6
    KATER__RANK(EVAL(shop.count_distinct__order_id), 'desc') OVER ()
7
  ) AS "Orders (Rank)" /* @format number(0) */
8
FROM workspace.shop AS shop
9
GROUP BY
10
  shop.region
11
QUALIFY
12
  KATER__IS_AT_MOST(KATER__RANK(EVAL(shop.count_distinct__order_id), 'desc') OVER (), 3)
13
ORDER BY
14
  3

APAC and the rows with no region rank 4 and 5, so the filter drops them. Top N on the measure gives the same three rows here, with QUALIFY KATER__IS_IN_TOP(EVAL(shop.count_distinct__order_id), 3). Top N keeps ties, so it can keep more than three rows.

Its own menu

A custom calculation’s menu offers less than a field from the model, because nothing can build on it.

• Its window reopens in its form. Edit calculation opens the New calculation dialog with the partition, the order and the settings it was written with. The partition is optional.
• Sort, format and edit. Sort ascending and Sort descending. Under Format, Round, Thousands Separator, Percent and Display format. Under Edit, Change label, Classification and Duplicate. A copy is a custom calculation too.
• A number format is a comment. Round, Thousands Separator and Percent write the field’s -- @format, not a function around the window. So the window still reopens in its form.
• Filter by this number. The fixed conditions above.
• Nothing that builds on it. No Aggregations, Derive, Timeframe or new Windows, and no filters but Filter by this number. SQL cannot run a window inside another, and a condition on a calculation is a calculation too.

Read a custom calculation from another query

A query can read another query of its workspace with FROM query.<name>. It gets each custom calculation as the value that query computed. Nothing is worked out again at the reader’s grouping, and the reader’s WHERE filters the finished values.

The field list inserts it bare, never with EVAL. In the reading query, its menu is a dimension’s menu for its data type, without Aggregations.

The examples read this query, monthly_units, which keeps a running total of Units by month:

1
SELECT
2
  shop.order_month,
3
  EVAL(shop.sum__quantity),
4
  CUSTOM_CALCULATION(
5
    'running_sum__sum__quantity',
6
    KATER__WINDOW_AGGREGATION(EVAL(shop.sum__quantity), 'sum', 'running') OVER (ORDER BY shop.order_month ASC)
7
  ) AS "Units (Running Sum)"
8
FROM workspace.shop AS shop
9
GROUP BY
10
  shop.order_month
11
ORDER BY
12
  1

Adding up a running total counts months again

1
SELECT
2
  KATER__YEAR(q.order_month) AS "Order Year",
3
  SUM(q.running_sum__sum__quantity) AS "Running Units Added Up"
4
FROM query.monthly_units AS q
5
GROUP BY
6
  KATER__YEAR(q.order_month)
7
ORDER BY
8
  1

The query runs, with the warning custom_calculation_reaggregated: “running_sum__sum__quantity is already computed by query monthly_units over its result rows, so SUM() here combines those results rather than computing it for these rows.” The shop sold 9,154 units in all, so neither number means anything. Each month’s units are in every later running total, so the sum counts them again and again.

The last running total of each year

A maximum reads the finished values as they are, with no warning:

1
SELECT
2
  KATER__YEAR(q.order_month) AS "Order Year",
3
  MAX(q.running_sum__sum__quantity) AS "Units So Far"
4
FROM query.monthly_units AS q
5
GROUP BY
6
  KATER__YEAR(q.order_month)
7
ORDER BY
8
  1

The running total only goes up, so its maximum is its value at the end of each year. By the end of 2025, the shop had sold 5,279 units, and 9,154 by the end of September 2026. For Units in each year, group the workspace’s Units by year instead.

Filter on the finished values

1
SELECT
2
  q.order_month,
3
  q.running_sum__sum__quantity
4
FROM query.monthly_units AS q
5
WHERE
6
  q.running_sum__sum__quantity >= 8000

The WHERE reads the running totals that monthly_units computed over all 21 months. It does not start the running total again from July.

EVAL(q.running_sum__sum__quantity) does not compile. The compiler says: “running_sum__sum__quantity is already computed by query monthly_units over its result rows, so EVAL can’t compute it again here. Select it without EVAL.”

Convert to a measure

A custom calculation stays in its query. When a measure gives the same numbers, the calculation’s menu offers Edit › Convert to a measure. It rewrites the calculation in place as that measure, and keeps its name and its label. A reader can then work out the value at any grouping, and you can promote it.

Each region’s share of Units

Windows › Share of Total on Units writes a custom calculation:

1
SELECT
2
  shop.region,
3
  EVAL(shop.sum__quantity),
4
  CUSTOM_CALCULATION(
5
    'share_of_total__sum__quantity',
6
    KATER__SHARE_OF_TOTAL(EVAL(shop.sum__quantity)) OVER ()
7
  ) AS "Units (Share of Total)" /* @format percent(1) */
8
FROM workspace.shop AS shop
9
GROUP BY
10
  shop.region
11
ORDER BY
12
  2 DESC

Convert to a measure rewrites it as Units divided by Units with the region removed from its context:

1
SELECT
2
  shop.region,
3
  EVAL(shop.sum__quantity),
4
  MEASURE(
5
    'share_of_total__sum__quantity',
6
    CAST(EVAL(shop.sum__quantity) AS DOUBLE) / NULLIF(EVAL(shop.sum__quantity, REMOVE(shop.region)), 0)
7
  ) AS "Units (Share of Total)" /* @format percent(1) */
8
FROM workspace.shop AS shop
9
GROUP BY
10
  shop.region
11
ORDER BY
12
  2 DESC

Both queries give the same rows:

The window divides each row’s Units by the sum of the five rows. The measure divides each region’s Units by Units with no region in its context, which is all 9,154 units. Both give 2,421 / 9,154 = 26.4% for EMEA. Only the measure gives a right answer at another grouping too.

What converts

• The numbers must match. A share converts only when its partition keeps every dimension that the measure cannot be added up across. Also, no filter can read a grouping key that REMOVE drops.
• A distinct count does not convert. Orders counts distinct order IDs, and distinct counts do not add up across groups. So a share of Orders stays a custom calculation.
• Nothing converts in a query with a join or a HAVING. There, the result rows are not the measure’s rows.
• Nothing else converts. The other windows depend on the result rows, so they never convert. That covers lag, lead, rank, row number, first and last value, delta and percent delta. It also covers running and trailing aggregations, and any window over a dimension.
• Promotion converts for you. When you promote a share of the total, Kater rewrites it as this measure first. See Promotion.

Common errors

Errors about where a window can go, such as invalid_window_placement, are on Running totals, ranks and windows.

Next

• Running totals, ranks and windows: the Windows menu, one example for each function.
• Period comparisons: the Compare with menu, against the previous period, last year or any shift you set.