3.3 Calculated Fields, Logical Functions & Parameters

3.3 Calculated Fields, Logical Functions & Parameters

Calculated fields allow you to create new data fields from existing data in your data source. Whether you need to extend raw data with custom business logic, segment text, manipulate dates, or make visualizations interactive using dynamic parameters, calculations are central to advanced data analysis in Tableau Desktop.

---

Row-Level vs. Aggregated Calculations

Understanding the distinction between Row-Level calculations and Aggregated calculations is mandatory for the Specialist exam. Mixing these two levels improperly generates the famous error: "Cannot mix aggregate and non-aggregate arguments in a calculation."

1. Row-Level Calculations

Row-level calculations evaluate an expression for every individual row (record) in your underlying data source independently before any visualization aggregation occurs.

• Example Formula: [Sales] - [Cost] or [First Name] + ' ' + [Last Name]
• Execution: Tableau computes Sales - Cost for row 1, row 2, row 3, etc., storing the result for each row. When dragged onto Rows or Columns, Tableau then aggregates those row-level results using SUM([Calculation]).
• Pill Prefix: Appears in the view as SUM(Profit Margin Row) or AGG(...) depending on field settings.

2. Aggregated Calculations

Aggregated calculations perform mathematical operations after rows have been grouped by the dimensions present in the visualization view.

• Example Formula: SUM([Profit]) / SUM([Sales])
• Execution: Tableau first sums all Profit for the category, then sums all Sales for the category, and finally divides SUM(Profit) by SUM(Sales).
• Pill Prefix: Always displays with the AGG prefix on shelves (e.g., AGG(Profit Ratio)), indicating that the aggregation is hardcoded inside the calculation and cannot be re-aggregated by Tableau.

Why it matters: Comparing SUM([Profit] / [Sales]) (Row-level calculation aggregated by SUM) vs. SUM([Profit]) / SUM([Sales]) (Aggregated calculation) yields drastically different mathematical results! Row-level sums the ratios, producing mathematically invalid profit margins.

---

Essential Tableau Functions

String Functions

String functions manipulate text fields in Tableau:

• UPPER(string) / LOWER(string): Converts text to uppercase or lowercase.
• LEFT(string, num_chars) / RIGHT(string, num_chars): Extracts characters from the start or end of a string.
• MID(string, start_pos, length): Extracts a substring starting at a specified character index.
• CONTAINS(string, substring): Returns TRUE if the string contains the target substring.
• REPLACE(string, target, replacement): Replaces occurrences of a substring.
• SPLIT(string, delimiter, token_number): Splits a string by delimiter and returns the nth token.

Date Functions

Dates are complex data types that Tableau handles with powerful built-in date functions:

• DATEPART('date_part', date): Extracts a specific part of a date as an integer (e.g., DATEPART('month', #2026-05-15#) returns 5).
• DATENAME('date_part', date): Extracts a date part as a string label (e.g., DATENAME('month', #2026-05-15#) returns 'May').
• DATETRUNC('date_part', date): Truncates the date to the accuracy of the specified date part, returning a full date representing the first day of that period (e.g., DATETRUNC('month', #2026-05-15#) returns #2026-05-01 00:00:00#).
• DATEADD('date_part', interval, date): Adds a specified numeric interval to a date.
• DATEDIFF('date_part', start_date, end_date): Returns the integer difference between two dates in the specified date part units.
• TODAY() / NOW(): Returns the current date or current date and time.

Logical Functions & Conditional Expressions

Logical functions evaluate conditions and return specific outcomes:

• IF-THEN-ELSE Structure:

IF [Sales] >= 10000 THEN "High Volume"
ELSEIF [Sales] >= 5000 THEN "Medium Volume"
ELSE "Low Volume"
END

• CASE Statement (simpler equality matching on a single field):

CASE [Region]
  WHEN "East" THEN 0.15
  WHEN "West" THEN 0.20
  ELSE 0.10
END

• Handling Nulls:
  • ISNULL(expression): Returns TRUE if expression is null.
  • IFNULL(expr1, expr2): Returns expr1 if non-null; otherwise returns expr2.
  • ZN(expression): Returns expression if non-null; if null, returns 0 (Zero Null).
• The ATTR() Aggregation:
  • ATTR(expression): Evaluates whether all records in the current visualization mark share an identical value. Returns the value if unique; returns * if multiple distinct values exist.

---

Parameters in Tableau Desktop

A Parameter is a dynamic, workbook-level variable (such as a number, date, or string) that replaces constant values in calculations, filters, sets, and reference lines.

Key Parameter Concepts

1. Workbook Scope: Parameters are global across the entire workbook and can be used across multiple worksheets and data sources.
2. Independent Controls: Creating a parameter does nothing by itself! A parameter only impacts the visualization when it is explicitly referenced inside a calculated field, filter, set, or reference line.
3. Data Types: Parameters support Data Types including Float, Integer, String, Boolean, Date, and Date & Time.
4. Allowable Values: Can be set to All, List (fixed set of choices), or Range (Min, Max, and Step size).

Practical Uses for Parameters

• Dynamic Measure Selection: A parameter allowing users to select between viewing Sales, Profit, or Quantity via a calculated field:

CASE [p_Select Measure]
  WHEN "Sales" THEN [Sales]
  WHEN "Profit" THEN [Profit]
  WHEN "Quantity" THEN [Quantity]
END

• Top N Filtering: Driving Top N filters dynamically (e.g., viewing Top 5, Top 10, or Top 20 Customers based on a parameter input slider).
• Reference Line Thresholds: Allowing users to adjust a benchmark target line on a chart interactively.

---

Calculations and Parameters Quick Reference