4.3 Calculations: Logic, Strings, Nulls & Type Conversion

4.3 Calculations: Logic, Strings, Nulls & Type Conversion

Calculated fields add derived values to Tableau's metadata without changing source rows. A calculation may operate row by row, aggregate records at the view's grain, use a level-of-detail expression, or operate as a table calculation. Keep all expressions at compatible aggregation levels and validate nulls and type conversions with representative values.

IF, ELSEIF, and CASE

Use IF and ELSEIF for ranges, compound Boolean conditions, or tests involving different fields:

IF [Sales] >= 100000 AND [Profit] > 20000 THEN 'High'
ELSEIF [Sales] >= 50000 THEN 'Medium'
ELSE 'Low'
END

Conditions are checked in order, so place a narrower high threshold before a broader lower threshold. If Sales is null, comparisons such as [Sales] >= 50000 do not evaluate true; decide explicitly whether null belongs in the Else result.

CASE compares one expression with a series of exact values:

CASE [Region]
  WHEN 'East' THEN 'Atlantic'
  WHEN 'West' THEN 'Pacific'
  ELSE 'Other'
END

CASE is concise for exact mappings. IF is clearer for ranges such as Sales > 1000 or combined conditions. There is no universal rule that CASE is faster: Tableau may translate calculations to a source language, and performance depends on the connector, expression, data, and source plan. Choose for correctness and readability, then measure if performance matters.

A Boolean expression can often stand alone. Instead of IF [Profit] > 0 THEN TRUE ELSE FALSE END, use [Profit] > 0. Remember that a null comparison can remain null rather than false.

Row-level and aggregate calculations

The expression [Sales] / [Quantity] is row-level. SUM([Sales]) / SUM([Quantity]) is aggregate and returns a weighted unit value at the view's grain. [Sales] / SUM([Quantity]) mixes a row-level value with an aggregate and is invalid. Resolve this by aggregating both sides or by intentionally de-aggregating the problem—not by wrapping an arbitrary field in ATTR merely to silence the error.

A ratio of sums usually differs from AVG([Sales] / [Quantity]). The first weights each row by quantity; the second gives every row-level ratio equal weight. Select the business definition before selecting the formula.

Null handling

Null represents missing or unknown data. Arithmetic with a null ordinarily returns null, so [Base Salary] + [Bonus] is null when Bonus is null. Use ZN([Bonus]) when the business rule genuinely treats missing numeric bonus as zero. IFNULL([Phone], 'No phone') supplies a fallback of the same compatible type. ISNULL tests whether a value is null.

Do not convert every null to zero automatically. Missing revenue, an actual zero sale, and a customer who is outside the applicable population have different meanings. Preserve that distinction when it affects counts, averages, or labels.

String concatenation also needs null handling. 'Customer: ' + IFNULL([Customer Name], 'Unknown') retains the prefix and produces a useful fallback. Applying IFNULL after the whole concatenation would return only the fallback when the name is null.

String functions

Common functions include:

• LEFT, RIGHT, and MID for fixed-position extraction.
• SPLIT(string, delimiter, token) for delimited text; positive tokens count from the left and negative tokens from the right.
• CONTAINS, STARTSWITH, and ENDSWITH for tests whose case behavior can depend on the source.
• TRIM, LTRIM, RTRIM, UPPER, and LOWER for normalization.
• REPLACE for literal substitution.

For a SKU PROD-US-WEST-9824-PREM, SPLIT([SKU], '-', 4) returns 9824. Fixed-position MID is less resilient if earlier segments change length. If delimiters or structures vary, a regular expression may be more appropriate.

Regular expressions

REGEXP_MATCH returns a Boolean result. REGEXP_REPLACE substitutes pattern matches. REGEXP_EXTRACT returns a captured substring, and REGEXP_EXTRACT_NTH returns the requested capture group from a match—not the nth occurrence of the overall pattern. Connector support varies, so check whether the active live source supports the chosen regular-expression function. Creating an extract can provide different function support when a live source lacks it.

Patterns should be anchored when the complete value must conform. For example, ^[0-9]{5}(-[0-9]{4})?$ tests a five-digit US postal code with an optional four-digit extension. Without anchors, a longer invalid string could still contain a matching substring.

Type conversion

INT converts to an integer and truncates a decimal rather than rounding it. FLOAT converts compatible values to decimal numbers. STR converts values to text. DATE converts compatible expressions to dates. DATEPARSE(format, string) interprets a string according to an explicit pattern where the connector supports it.

A failed parse can produce null. Before converting a field, identify invalid strings, locale differences, ambiguous month/day order, and identifiers that require leading zeros. Postal codes and account IDs commonly belong as strings even when every character is numeric.

Use ROUND when rounding is intended. INT(9.99) produces 9, while ROUND(9.99, 0) produces 10. Avoid converting dates to strings merely to format them; use date formatting so sorting and date functions remain available.

Date, number, and aggregate functions together

Calculations can combine types only through valid conversions and compatible branches. Every return branch of IF or CASE must resolve to a compatible data type. A metric-switching CASE cannot return SUM(Sales) for one option and ATTR(Customer Name) for another. Build separate display fields or convert intentionally to strings when the result is only a label.

Validation workflow

1. Write the business rule in plain language and specify the required grain.
2. Inspect source data types and null meanings.
3. Decide whether the calculation is row-level, aggregate, LOD, or table calculation.
4. Test boundary values, nulls, zero denominators, invalid strings, and mixed case.
5. Compare totals against an independent control calculation.
6. Name and format the field so its aggregation and unit are clear.

Calculation syntax can be valid while the business meaning is wrong. Grain, null policy, and denominator choice are part of the formula.