Tableau function Operators
[a] + [b] adds two numbers, and a null on either side makes the result null, in Tableau and in SQL alike. The trap is aggregation: SUM([a] + [b]) drops whole rows where either value is null, while SUM([a]) + SUM([b]) keeps every value.
a + ba, bReturns a number. Null if either side is null.
Each example runs on the same 8 sample rows twice: the Tableau formula on the xmetric engine, and the SQL in DuckDB.
Tableau
[a] + [b]SQL (DuckDB)
SELECT a + b AS result
FROM input
ORDER BY idRows 6 and 7 each miss one value, so they return null.
| a | b | Result |
|---|---|---|
| 7 | 2 | 9 |
| −7 | 2 | −5 |
| 2 | 5 | 7 |
| 9 | 4 | 13 |
| 5 | 3 | 8 |
| 2 | null | |
| 1 | null | |
| −3 | −2 | −5 |
The Tableau formula and the SQL return the same 8 rows.
Tableau
[a] + [x]SQL (DuckDB)
SELECT a + x AS result
FROM input
ORDER BY idThe result is a decimal: row 1 is 8.5.
| a | x | Result |
|---|---|---|
| 7 | 1.5 | 8.5 |
| −7 | −2.5 | −9.5 |
| 2 | 0.1 | 2.1 |
| 9 | 2.25 | 11.25 |
| 5 | null | |
| 4 | null | |
| 1 | −1 | 0 |
| −3 | 3 | 0 |
The Tableau formula and the SQL return the same 8 rows.
Common mistake
sum(a) + sum(b) bigger than SUM([a] + [b])?Inside SUM, a + b is null on rows 6 and 7, so their other value is lost too: 22. Summing each column first keeps them: 25. Keep the SUM where Tableau has it.
Tableau
SUM([a] + [b])Tempting SQL
SELECT sum(a) + sum(b) AS result
FROM inputSQL that matches
SELECT sum(a + b) AS result
FROM input| Tableau | Tempting SQL | SQL that matches |
|---|---|---|
| 27 | 30 | 27 |
The SQL that matches returns the Tableau result on every row; the marked cells are where the tempting SQL differs.
The first example, written for each warehouse from its documentation. These are not run here; DuckDB is.
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.
ZN([a]) + ZN([b]); in SQL, coalesce(a, 0) + coalesce(b, 0).
Yes. [a] + [x] returns a decimal: 7 + 1.5 is 8.5.
Yes: see + (string) and + (date).