EXCEL

Family: Field declarations

The compiler turns the formula into your warehouse’s SQL each time the query compiles.

Syntax

1
CUSTOM_CALCULATION('name', EXCEL('=formula')) AS "Label"

Arguments

Returns

The formula’s value for each row of the result.

Usage notes

• Write it in a query or in the Sandbox, inside CUSTOM_CALCULATION. It is the field’s whole value, never part of a larger expression. Shared models have no formulas.
• A formula reads only the columns of its own query. Add a field to the query before a formula reads it.
• [@revenue] reads this row, and [@revenue:-1] the row above. [revenue] reads every row, and [#total revenue] the measure worked out over every row at once.
• The row above follows the query’s ORDER BY. A formula that reads another row needs the query sorted.
• The syntax is US Excel’s, with a comma between arguments. A value that Excel shows as an error, such as a division by zero, is empty.
• Lookups such as VLOOKUP and XLOOKUP are not available. Bring the value into the query as a field.

Example

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

1
SELECT
2
  orders.channel AS "Channel",
3
  EVAL(orders.sum__quantity) AS "Units",
4
  EVAL(orders.count_distinct__order_id) AS "Orders",
5
  CUSTOM_CALCULATION('units_per_order', EXCEL('=ROUND([@sum__quantity]/[@count_distinct__order_id], 2)')) AS "Units per Order"
6
FROM kater_dataset.orders AS orders
7
GROUP BY
8
  orders.channel
9
ORDER BY
10
  1

See also

• Fields
• Other functions in Field declarations: DIMENSION, MEASURE, CUSTOM_CALCULATION, NOT_ADDITIVE_ACROSS