xmetricFunctions

Tableau function Operators

Tableau > (greater) to SQL

[a] > [b] is true when a is larger, false when it isn't, null when either is missing, as in SQL. The trap sits around it: IIF([a] > [b], ...) returns null for those rows, while a SQL CASE sends them to ELSE.

Updated · Checked on the xmetric engine

What is the syntax?

a > b
a, b
two values of the same kind: numbers, strings, dates or booleans.

Returns true, false, or null when 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.

Which rows pass a > b?

Tableau

[a] > [b]

SQL (DuckDB)

SELECT a > b AS result
FROM input
ORDER BY id

Row 2 (-7 > 2) is false; rows 6 and 7 are null.

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

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

How does > compare two text fields?

Tableau

[s] > [t]

SQL (DuckDB)

SELECT lower(s) > lower(t) AS result
FROM input
ORDER BY id

Case is ignored, so row 1 (West, west) compares as equal text.

stResult
Westwestfalse
EastNorthfalse
NorthNorthfalse
southEasttrue
Eastnull
Westnull
EastSouthfalse
easteastfalse

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

Where does a straight translation go wrong?

Common mistake

Why does the CASE return "no" where Tableau returns null?

IIF returns null when its test is null (row 6 has no a, row 7 no b). A SQL CASE sends a null test to ELSE. Add a WHEN NOT branch so nulls fall through to null.

Tableau

IIF([a] > [b], 'yes', 'no')

Tempting SQL

SELECT CASE WHEN a > b THEN 'yes' ELSE 'no' END AS result
FROM input
ORDER BY id

SQL that matches

SELECT
  CASE
    WHEN a > b THEN 'yes'
    WHEN NOT (a > b) THEN 'no'
    END AS result
FROM input
ORDER BY id
abTableauTempting SQLSQL that matches
72yesyesyes
−72nonono
25nonono
94yesyesyes
53yesyesyes
2nullnonull
1nullnonull
−3−2nonono

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 LOWER(s) > LOWER(t) AS result
FROM input
ORDER BY id

Snowflake documentation

BigQuery

SELECT LOWER(s) > LOWER(t) 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 >?

What happens when one side is null?

The comparison returns null, not false, in Tableau and in SQL. A filter drops those rows, and IIF returns null for them.

Is the string comparison case sensitive?

On an extract or a file source, no: Tableau's default string collation ignores case, so West and west compare as equal. On a live connection the database's collation decides. DuckDB compares case sensitively, so lower both sides to match an extract.

Can I compare dates with >?

Yes: [d] > [e] is true when d is later.

Where is this documented?