xmetricFunctions

Tableau function Operators

Tableau / (divide) to SQL

[a] / [b] divides and returns a decimal even when both are integers: 7 / 2 is 3.5. DuckDB's / does the same, but many databases truncate integer division, so cast there. For ratios, keep Tableau's shape: SUM([a]) / SUM([b]) is not the average of row ratios.

Updated · Checked on the xmetric engine

What is the syntax?

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

Returns a decimal 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

7 / 2 is 3.5, not 3.

abResult
723.5
−72−3.5
250.4
942.25
531.67
2null
1null
−3−21.5

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

What is the ratio of the totals?

Tableau

SUM([a]) / SUM([b])

SQL (DuckDB)

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

Total a (14) over total b (16).

Result
0.88

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

Where does a straight translation go wrong?

Common mistake

Why is avg(a / b) not the ratio of the totals?

Averaging row ratios weights every row equally; SUM([a]) / SUM([b]) weights them by b. They give 0.97 and 0.875 here.

Tableau

SUM([a]) / SUM([b])

Tempting SQL

SELECT avg(a / b) AS result
FROM input

SQL that matches

SELECT sum(a) / sum(b) AS result
FROM input
TableauTempting SQLSQL that matches
0.880.970.88

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

Common mistake

Why does floor(a / b) lose the fraction?

That's what integer division does in many databases. Tableau's / keeps 3.5; use DIV when you want the whole part.

Tableau

[a] / [b]

Tempting SQL

SELECT floor(a / b) AS result
FROM input
ORDER BY id

SQL that matches

SELECT a / b AS result
FROM input
ORDER BY id
abTableauTempting SQLSQL that matches
723.533.5
−72−3.5−4−3.5
250.400.4
942.2522.25
531.6711.67
2nullnullnull
1nullnullnull
−3−21.511.5

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 DIV0NULL(a, b) AS result
FROM input
ORDER BY id

Snowflake documentation

BigQuery

SELECT SAFE_DIVIDE(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 /?

Does 7 / 2 give 3 or 3.5?

3.5 in Tableau and DuckDB. SQL Server and PostgreSQL return 3 for two integers; cast one side to a decimal there.

How do I avoid dividing by zero?

Guard it: IIF([b] = 0, NULL, [a] / [b]). In SQL, a / nullif(b, 0); BigQuery has SAFE_DIVIDE and Snowflake DIV0NULL.

What if either side is null?

The result is null, as for the other arithmetic operators.

Where is this documented?