xmetricFunctions

Tableau function Operators

Tableau * (multiply) to SQL

[a] * [x] multiplies, with null when either side is null, the same as SQL. When translating an aggregate, keep the product inside the SUM: SUM([a] * [x]) sums row products, which is not the product of two sums.

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 * x on each row?

Tableau

[a] * [x]

SQL (DuckDB)

SELECT a * x AS result
FROM input
ORDER BY id

Row 2 is -7 * -2.5 = 17.5.

axResult
71.510.5
−7−2.517.5
20.10.2
92.2520.25
5null
4null
1−1−1
−33−9

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(x) give 102.9?

Multiplying the totals is a different number from summing row products (38.45). Translate SUM([a] * [x]) as sum(a * x).

Tableau

SUM([a] * [x])

Tempting SQL

SELECT sum(a) * sum(x) AS result
FROM input

SQL that matches

SELECT sum(a * x) AS result
FROM input
TableauTempting SQLSQL that matches
38.4510338.45

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

Snowflake documentation

BigQuery

SELECT a * x 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 compute a weighted average?

SUM([a] * [x]) / SUM([x]); in SQL, sum(a * x) / sum(x).

Does integer times decimal stay an integer?

No. [a] * [x] is a decimal: 7 * 1.5 is 10.5.

Where is this documented?