Đại học Kinh tế Quốc dân – National Economics UniversityMICRO4ASIA
🏆 Arena
Sign inSign up
MICRO4ASIAThe six micro-credentials

Courses are delivered on the Limio learning platform. Limio

← Data Visualization in Business

You are viewing a public preview

Sign in to save your progress, do assignments, join discussions and get feedback.

Sign in
Module 4 — Building Advanced Charts and Analyzing Data in Tableau

4.2 · Advanced Calculation and Analysis Techniques

Print / Save as PDF

In this lesson

Calculated fields and business metrics; conditional, string, date and aggregate functions; table calculations such as running total, percent of total, rank, difference and percent difference; Level of Detail expressions: FIXED, INCLUDE and EXCLUDE.

Learning outcomes

  • LO.5: Use Tableau Desktop and Tableau Public to connect data, organize fields and build visualizations.
  • LO.6: Select, build and refine charts that fit the analytical objective and the data.
  • LO.7: Apply calculations and analytical techniques to explore business data and interpret findings.

The lecture slides for this lesson are below.

Study notes

By the end of this lesson you can:

  • Create calculated fields with logical, string, date and aggregate functions.
  • Explain the difference between row-level and aggregate calculations and choose the right one for a business metric.
  • Apply table calculations (running total, percent of total, rank, difference, percent difference) and control how they compute.
  • Write FIXED, INCLUDE and EXCLUDE Level of Detail expressions and explain when each is needed.

Key idea: Where a calculation happens matters as much as the formula. Row-level calculations run on each record, aggregate calculations run on groups, table calculations run on the numbers already in the view, and LOD expressions let you choose the level of detail yourself.

Calculated fields

A calculated field is a custom field created by applying formulas or expressions to existing fields. It lets you compute values, combine data or apply conditions that are not directly in the dataset. You create one with Analysis → Create Calculated Field (or right-click in the Data pane); the editor tells you whether "The calculation is valid".

The lecture's example uses two date dimensions, Order Date and Ship Date:

Number of Days = [Ship Date] - [Order Date]

Subtracting two dates returns the number of days between them; DATEDIFF('day', [Order Date], [Ship Date]) gives the same result and lets you choose other units.

Common function groups:

GroupExamplesUse
LogicalIF ... THEN ... ELSEIF ... ELSE ... END, CASE, IIF, IFNULL, ZNBands, flags, handling NULLs
StringLEFT, CONTAINS, UPPER, TRIM, SPLIT, REPLACECleaning and parsing text
DateDATEDIFF, DATEADD, DATETRUNC, DATEPART, YEARDurations, periods, cohorts
AggregateSUM, AVG, MIN, MAX, COUNT, COUNTDBusiness metrics

Row-level versus aggregate calculations

A row-level calculation is computed for every record, then aggregated in the view: [Sales] - [Profit] gives a cost per order line. An aggregate calculation is computed for each group in the view: SUM([Profit]) / SUM([Sales]) gives the profit ratio of whatever the mark represents (a category, a month). You cannot mix the two in one expression without aggregating everything (Tableau reports "cannot mix aggregate and non-aggregate arguments").

Ratios are the classic trap: AVG([Profit] / [Sales]) averages the ratio of each line, so a tiny order counts as much as a huge one. The business profit ratio is the aggregate form, SUM([Profit]) / SUM([Sales]).

Table calculations

A table calculation is computed on the aggregated values already in the view. Quick Table Calculation offers running total, difference, percent difference, percent of total, rank, percentile, moving average, YTD total, compound growth rate and year-over-year growth. A triangle (delta) on the pill marks a table calculation.

What matters is Compute Using: the direction or dimensions along which the calculation runs (Table across, Table down, Pane, Cell, or specific dimensions). The dimensions it runs along are addressing; the others are partitioning, and the calculation restarts for each partition. In Edit Table Calculation you can choose Restarting every a dimension, for example restart a running total each year.

Level of Detail expressions

LOD expressions compute an aggregate at a level of detail you specify, independent of (or relative to) the view:

  • {FIXED [Customer ID] : MIN([Order Date])}: one value per customer, whatever is in the view (here the first order date).
  • {INCLUDE [Customer ID] : SUM([Sales])}: adds Customer ID to the view's dimensions, then the view aggregates the results, for example AVG of customer sales per Region.
  • {EXCLUDE [Sub-Category] : SUM([Sales])}: removes a dimension from the view's level, for example category totals shown beside sub-category bars.

Order of operations matters: FIXED expressions are computed before ordinary dimension filters (only context, data source and extract filters affect them); INCLUDE and EXCLUDE are computed after dimension filters; table calculations come last, on the results in the view.

Worked example

The customer team asks: "How long do we take to ship, and how many customers buy more than once?" Use the course Orders table.

  1. Shipping time (row level). Create Days to Ship = DATEDIFF('day', [Order Date], [Ship Date]). Put Ship Mode on Rows and AVG(Days to Ship) on Columns. Each mark averages over the order lines of one ship mode. By definition you expect Same Day near 0; if it is not, the data or the process need checking.
  2. A banded field (logical).
IF [Days to Ship] = 0 THEN "Same day"
ELSEIF [Days to Ship] <= 3 THEN "1-3 days"
ELSE "4+ days"
END

Use it on Color to show the mix of delivery speeds per Region. 3. Orders per customer (FIXED LOD). Orders are stored per order line, so counting rows would overcount. Create Orders per Customer = {FIXED [Customer ID] : COUNTD([Order ID])}. This gives one value per customer regardless of the view. 4. Distribution. Convert Orders per Customer to a discrete dimension, put it on Columns and COUNTD(Customer ID) on Rows. The bar chart shows how many customers placed 1, 2, 3 ... orders, which answers "how many buy more than once?". 5. Share of repeat customers (table calculation). Apply Percent of Total to COUNTD(Customer ID), computed across the table, to read the share of customers in each bar. 6. Check filters. If you add a Region filter, the FIXED values ignore it unless you add the filter to context; decide which behaviour the question needs.

Common mistakes

  • Averaging row-level ratios. Use SUM([Profit]) / SUM([Sales]), not AVG([Profit] / [Sales]).
  • Wrong Compute Using. A percent of total computed down instead of across gives shares that do not add up the way you expect; check the direction.
  • Expecting FIXED to respect dimension filters. Use a context filter, or INCLUDE/EXCLUDE, if the filter must apply first.
  • Counting rows as orders. Use COUNTD([Order ID]) in a line-level table.

Summary

  • Calculated fields create new fields from existing fields with logical, string, date and aggregate functions.
  • Row-level calculations run per record; aggregate calculations run per mark; ratios must be aggregated.
  • Table calculations work on the values in the view and depend on Compute Using and restarts.
  • FIXED, INCLUDE and EXCLUDE set the level of detail of a calculation explicitly.
  • FIXED runs before dimension filters; INCLUDE/EXCLUDE after; table calculations last.

Flashcards · Advanced Calculation and Analysis Techniques

10 words
WordMeaningExample
  • Calculated field

    A custom field created with a formula on existing fields to compute values or apply conditions.

    Number of Days = [Ship Date] - [Order Date]

  • DATEDIFF

    Returns the difference between two dates in the chosen unit.

    DATEDIFF('day', [Order Date], [Ship Date])

  • Aggregate calculation

    A calculation computed per group in the view; needed for correct ratios.

    SUM([Profit]) / SUM([Sales])

  • Table calculation

    A calculation applied to the aggregated values already in the view.

    Running Total of SUM(Sales) by month.

  • Compute Using

    The direction or dimensions along which a table calculation is computed.

    Running total restarting every YEAR(Order Date).

  • Percent of Total

    A table calculation showing each mark's share of the total in its partition.

    Sales by Region as a share of all sales.

  • FIXED LOD

    Computes an aggregate at the specified dimensions, ignoring the view's dimensions.

    {FIXED [Customer ID] : MIN([Order Date])}

  • INCLUDE LOD

    Adds dimensions to the view's level before aggregating, then the view re-aggregates.

    {INCLUDE [Customer ID] : SUM([Sales])}

  • EXCLUDE LOD

    Removes dimensions from the view's level when computing an aggregate.

    {EXCLUDE [Sub-Category] : SUM([Sales])}

  • COUNTD

    Counts distinct values, avoiding overcounts in line-level tables.

    COUNTD([Order ID]) = number of orders.

Practice in Tableau — Lab 4B: Calculations and LOD expressions

  1. Profit Ratio. SUM([Profit]) / SUM([Sales]). Check: 12.2% overall; Furniture only 0.3%, Office Supplies 20.2%, Technology 17.0%. Why is it wrong to average a row-level ratio instead?
  2. Conditional field. Discount Band: IF [Discount] = 0 THEN "No discount" ELSEIF [Discount] <= 0.2 THEN "Up to 20%" ELSE "Over 20%" END.
  3. Running total. Monthly sales with a Running Total table calculation; restart every year.
  4. Percent of total. Sales by Region as Percent of Total. Check: North 39.2%, South 37.9%, Central 22.9%.
  5. Year-over-year growth. YEAR(Order Date) × Sales with Percent Difference. Check: 14.1% in 2024 and 13.9% in 2025.
  6. Rank. Rank sub-categories by Sales with a Rank table calculation.
  7. FIXED LOD: first purchase. {FIXED [Customer ID] : MIN([Order Date])}, then YEAR of it. Check: customers by first-order year: 2023 = 189, 2024 = 28, 2025 = 9.
  8. FIXED LOD: order value. {FIXED [Order ID] : SUM([Sales])}. Check: 860 orders are worth more than 1,000; the average order value is 1,022.85.
  9. INCLUDE versus EXCLUDE. Show average customer sales per Region with {INCLUDE [Customer ID] : SUM([Sales])} and explain how it differs from AVG(Sales).