Study guide

4.3 Calculations: Logic, Strings, Nulls & Type Conversion

Salesforce Certified Tableau Data Analyst Exam

Track your progress as you read

Finished sections get a check mark.

1 Chapter 1: Connecting to Data & The Logical Data Model

1.1 Data Connections: Live Connections vs. Hyper Extracts 1.2 The Tableau Data Model: Relationships vs. Physical Joins 1.3 Combining Data: Data Blending vs. Cross-Database Joins

2 Chapter 2: Tableau Prep Builder & Data Wrangling

2.1 Tableau Prep Architecture & Data Cleaning 2.2 Reshaping Data in Prep: Pivots, Splits & Wildcard Unions 2.3 Joins, Aggregations & Output Destinations in Prep

3 Chapter 3: Data Quality, Metadata & Field Configuration

3.1 Data Types, Roles, Organization & Default Properties 3.2 In-Desktop Data Cleaning: Splits, Data Interpreter & Source Replacement 3.3 Managing Slowly Changing Dimensions & Data Quality Warnings

4 Chapter 4: Tableau Fundamentals & Calculations

4.1 Dimensions vs. Measures & Discrete vs. Continuous 4.2 Mastering Dates: Discrete Date Parts vs. Continuous Date Truncations 4.3 Calculations: Logic, Strings, Nulls & Type Conversion 4.4 Aggregation Rules, Granularity & Dealing with the Asterisk (ATTR)

5 Chapter 5: Level of Detail (LOD) Expressions

5.1 Level of Detail Architecture & Scoping Syntax 5.2 FIXED LOD Expressions: Scoping & Baseline Benchmarks 5.3 INCLUDE & EXCLUDE LOD Expressions: Dynamic Granularity 5.4 Real-World LOD Patterns: Cohorts, First/Last Dates & Binning

6 Chapter 6: Table Calculations & Directionality

6.1 Table Calculation Mechanics: Addressing vs. Partitioning 6.2 Essential Table Calculations: Running, Window & Percent of Total 6.3 Navigation & Ranking Functions: LOOKUP, INDEX & RANK 6.4 Secondary & Nested Table Calculations: Multi-Level Scoping

7 Chapter 7: Statistical Analytics, Filtering & Order of Operations

7.1 The Tableau Order of Operations: From Extract to Display Filters 7.2 Sets, Set Actions & Combined Sets vs. Groups 7.3 Parameters, Parameter Actions & Dynamic What-If Analysis 7.4 Analytics Pane: Totals, References, Trends & Forecasts

8 Chapter 8: Chart Types & Comparative Visualizations

8.1 Comparison Visualizations: Bar Charts, Highlight Tables & Bullet Graphs 8.2 Part-to-Whole & Correlation Charts: Treemaps, Scatter Plots & Bubbles 8.3 Trend & Distribution Visualizations: Dual-Axis, Combos & Histograms

9 Chapter 9: Advanced Visualizations & Spatial Analysis

9.1 Specialized Visualizations: Waterfall, Gantt, Sparklines & Bump Charts 9.2 Spatial Data & Mapping: Geographic Layers & Spatial Functions 9.3 Visual Polish: Custom Palettes, Annotations & Viz in Tooltip

10 Chapter 10: Dashboard Design & Interactive Actions

10.1 Dashboard Layout, Devices & Visibility 10.2 Filter, Highlight & URL Actions 10.3 Advanced Interactivity: Parameter, Set & Navigation Actions 10.4 Stories & Visual Storytelling: Sequencing Analytical Narratives

11 Chapter 11: Tableau Server & Cloud: Architecture & Publishing

11.1 Tableau Server & Cloud: Sites, Projects, Roles & Permissions 11.2 Publishing Workbooks, Data Sources & Flows

12 Chapter 12: Data Governance, Security & Operations

12.1 Certification, Data Quality, Lineage & Permissions 12.2 Row-Level Security, Schedules, Subscriptions & Alerts

4.3 Calculations: Logic, Strings, Nulls & Type Conversion

Key Takeaways Explain This Topic AI

Use CASE for exact mappings of one expression and IF or ELSEIF for ranges and compound conditions.

Do not mix row-level and aggregate fields in one expression; choose the calculation grain deliberately.

ZN and IFNULL implement explicit null policies, but missing values should not automatically become zero.

String and regular-expression functions differ in purpose and connector support.

Type-conversion failures can yield null, and numeric-looking identifiers often need to remain strings.

Last updated: September 2026

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

Write the business rule in plain language and specify the required grain.

Inspect source data types and null meanings.

Decide whether the calculation is row-level, aggregate, LOD, or table calculation.

Test boundary values, nulls, zero denominators, invalid strings, and mixed case.

Compare totals against an independent control calculation.

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.

Loading diagram... Calculated Field Architecture & Evaluation Pipeline