KATER__TOTAL

Family: Totals

Syntax

1
KATER__TOTAL(field) OVER ([PARTITION BY ...])

Arguments

Returns

A number in the unit of the value. The field menu writes the @format of the field it reads beside it, or @format number(2) where that field has none and can have decimals.

Usage notes

• Write OVER (PARTITION BY ...) to keep the fields each total is for, such as the currency, or OVER () for the grand total. The fields are fields the query groups by.
• It compiles to TOTAL(field, BY(...)). A measure is worked out again over all the rows, so the total of a ratio is the ratio of the totals. Any other number is added up over the query’s rows, as SUM(field) OVER (PARTITION BY ...).
• A measure that cannot be added up across a dimension, such as Revenue across Currency, keeps that dimension in PARTITION BY, unless a filter holds the query to one value. So does a number made from one, such as a locked-in measure or a comparison.
• A total counts every row that HAVING, QUALIFY and LIMIT would take out, so a query with a total has none of them.
• Declare it with MEASURE when it totals a measure, such as MEASURE('total__google_ads_spend', ...), and with CUSTOM_CALCULATION when it adds up the rows.

Example

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

1
SELECT
2
  orders.currency AS "Currency",
3
  orders.channel AS "Channel",
4
  EVAL(orders.sum__amount) AS "Revenue",
5
  MEASURE(
6
    'total__sum__amount',
7
    KATER__TOTAL(orders.sum__amount) OVER (PARTITION BY orders.currency)
8
  ) AS "Revenue (Total)" /* @format currency(currency, 2) */
9
FROM kater_dataset.orders AS orders
10
WHERE
11
  KATER__IS_ONE_OF(orders.channel, 'email', 'paid', 'web')
12
GROUP BY
13
  orders.currency,
14
  orders.channel
15
ORDER BY
16
  1,
17
  2

See also

• Charts
• Other functions in Totals: KATER__ROW_TOTAL