xmetricFunctions

Tableau function Operators

Tableau - (subtract) to SQL

[a] - [b] subtracts b from a; a null on either side gives null, as in SQL. Watch the aggregation when you translate: SUM([a] - [b]) skips rows where either value is null, SUM([a]) - SUM([b]) does not.

Updated · Checked on the xmetric engine

What is the syntax?

a - b
a, b
numbers, numeric fields or aggregates.

Returns a number. Null if either side is null.

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.

What is a - b on each row?

Tableau

[a] - [b]

SQL (DuckDB)

SELECT a - b AS result
FROM input
ORDER BY id

Row 2 is -7 - 2 = -9; row 8 is -3 - (-2) = -1.

abResult
725
−72−9
25−3
945
532
2null
1null
−3−2−1

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

Where does a straight translation go wrong?

Common mistake

Why does sum(a) - sum(b) give a different total?

SUM([a] - [b]) loses rows 6 and 7, where one side is null: -1. Subtracting the column totals keeps them: -2.

Tableau

SUM([a] - [b])

Tempting SQL

SELECT sum(a) - sum(b) AS result
FROM input

SQL that matches

SELECT sum(a - b) AS result
FROM input
TableauTempting SQLSQL that matches
−1−2−1

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 AS result
FROM input
ORDER BY id

Snowflake documentation

BigQuery

SELECT a - b AS result
FROM input
ORDER BY id

BigQuery documentation

What does your own - 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 -?

How do I keep rows where b is missing?

[a] - ZN([b]); in SQL, a - coalesce(b, 0).

Does - work on dates?

Yes: see - (date, date) and - (date, days).

Where is this documented?