xmetricFunctions

Tableau function Operators

Tableau % (modulo) to SQL

[a] % [b] is the remainder of a divided by b, with the sign of a: -7 % 2 is -1. DuckDB's % and mod() agree. The floor-based formula a - b * floor(a / b), Python's convention, gives 1 instead.

Updated · Checked on the xmetric engine

What is the syntax?

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

Returns the remainder, with the sign of a. 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 the remainder of a divided by b?

Tableau

[a] % [b]

SQL (DuckDB)

SELECT a % b AS result
FROM input
ORDER BY id

Row 2: -7 % 2 is -1. Row 8: -3 % -2 is -1.

abResult
721
−72−1
252
941
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 the floor formula return 1 for -7 % 2?

a - b * floor(a / b) rounds the quotient down, so the remainder takes the sign of b. Tableau truncates toward zero and keeps the sign of a.

Tableau

[a] % [b]

Tempting SQL

SELECT a - b * 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
72111
−72−11−1
25222
94111
53222
2nullnullnull
1nullnullnull
−3−2−1−1−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 MOD(a, b) AS result
FROM input
ORDER BY id

Snowflake documentation

BigQuery

SELECT MOD(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 test for even numbers?

[a] % 2 = 0; for negatives the remainder is 0 or -1, so test != 0 for odd, not = 1.

What if either side is null?

Null. Rows 6 and 7 of the sample return null.

Where is this documented?