xmetricFunctions

Tableau function Operators

Tableau operator precedence in SQL

Tableau evaluates operators in this order: unary -; ^; *, /, %; +, -; comparisons; NOT; AND; OR. DuckDB uses the same order, so [a] + [b] * 2 and [p] OR [q] AND NOT [p] translate as written. The one surprise is the minus sign: it binds tighter than ^, which a power() rewrite loses.

Updated · Checked on the xmetric engine

What is the syntax?

- (negate), ^, * / %, + -, comparisons, NOT, AND, OR
levels
unary minus binds tightest; OR binds loosest. Use parentheses to change the order.

Returns the order in which a formula with several operators is evaluated, highest first.

What does it return, and what is the SQL?

Each example runs on the same 8 sample rows twice: the Tableau formula on the xmetric engine, and the SQL in DuckDB.

Is multiplication done before addition?

Tableau

[a] + [b] * 2

SQL (DuckDB)

SELECT a + b * 2 AS result
FROM input
ORDER BY id

Row 1: 7 + 4 = 11, not 18.

abResult
7211
−72−3
2512
9417
5311
2null
1null
−3−2−7

The Tableau formula and the SQL return the same 8 rows.

Is AND evaluated before OR?

Tableau

[a] = [b] OR [a] > 5 AND [b] < 3

SQL (DuckDB)

SELECT a = b OR a > 5 AND b < 3 AS result
FROM input
ORDER BY id

Read as a = b OR (a > 5 AND b < 3): only row 1 passes.

abResult
72true
−72false
25false
94false
53false
2null
1null
−3−2false

The Tableau formula and the SQL return the same 8 rows.

Where does a straight translation go wrong?

Common mistake

Why does moving ^ into power() change the sign?

The minus is the tightest operator, so -[a] ^ 2 + [b] squares -a. -power(a, 2) + b negates the square instead.

Tableau

-[a] ^ 2 + [b]

Tempting SQL

SELECT - power(a, 2) + b AS result
FROM input
ORDER BY id

SQL that matches

SELECT power(- a, 2) + b AS result
FROM input
ORDER BY id
abTableauTempting SQLSQL that matches
7251−4751
−7251−4751
25919
9485−7785
5328−2228
2nullnullnull
1nullnullnull
−3−27−117

The SQL that matches returns the Tableau result on every row; the marked cells are where the tempting SQL differs.

How do I write it in Snowflake or BigQuery?

The first example, written for each warehouse from its documentation. These are not run here; DuckDB is.

Snowflake

SELECT a + b * 2 AS result
FROM input
ORDER BY id

Snowflake documentation

BigQuery

SELECT a + b * 2 AS result
FROM input
ORDER BY id

BigQuery documentation

What does your own precedence formula return?

Edit the formula and run it on the 8 sample rows. Fields: [id], [a], [b], [x], [p], [q], [s], [t], [d], [e].

Runs in your browser on the xmetric engine. Nothing is uploaded.

Which functions go with it?

What do people ask about precedence?

Does AND bind tighter than OR?

Yes, in Tableau and in SQL: [p] OR [q] AND [r] is [p] OR ([q] AND [r]).

Where does NOT sit?

Below comparisons, so NOT [a] > 5 is NOT ([a] > 5), the same as SQL.

Operators of the same level?

They run left to right: [a] / [b] * 2 is ([a] / [b]) * 2.

Where is this documented?