Skip to content
Tutorials Center
My learning

Topics

Lesson 35 of 35 ยท Practice

Tableau Cheat Sheet: Functions, Shortcuts and Rules

A one-page Tableau cheat sheet: pill colours, calculated field functions, LOD and table calculations, filter order, shortcuts and SQL equivalents you can run.

This is the page to keep open while you work: pill colours and shelves, the functions you use every day, the order of operations, a shortlist of keyboard shortcuts and a table of SQL and pandas equivalents so you can verify what you build. Tableau itself cannot run in the browser, so formulas are read-only. The two runnable blocks at the end are stand-ins that show the SQL and pandas counterparts of common Tableau expressions.

Pills at a glance

Blue Discrete. Makes headers.
Green Continuous. Makes an axis.
Dimension Categories that split the data.
Measure Numbers that get aggregated.
1 / 2

Shelves and marks

Place What it does
Columns / Rows Structure of the view (axes or headers)
Filters Restrict rows (extract, data source, context, normal)
Pages Animates the view through a dimension
Color, Size, Label Encode a field visually
Detail Sets granularity without a visible encoding
Tooltip Text shown on hover, including viz-in-tooltip
Shape, Angle Marks (shape, pie angle)

Mark types: Automatic, Bar, Line, Area, Square, Circle, Shape, Text, Map, Pie, Gantt Bar, Polygon, Density.

Common functions

Logical: IF cond THEN a ELSEIF cond2 THEN b ELSE c END, IIF(cond, a, b), CASE [Field] WHEN 'x' THEN 1 ELSE 0 END, AND, OR, NOT, IN, ISNULL, IFNULL(a, b), ZN(x).

Number: ABS, ROUND(x, n), CEILING, FLOOR, SQRT, POWER, LOG, MIN, MAX, DIV, % (modulo).

String: LEFT, RIGHT, MID, LEN, UPPER, LOWER, TRIM, CONTAINS, STARTSWITH, ENDSWITH, REPLACE, SPLIT([Field], '-', 2), FIND, REGEXP_EXTRACT, REGEXP_REPLACE.

Date: TODAY(), NOW(), YEAR, MONTH, DAY, DATEPART('quarter', d), DATETRUNC('month', d), DATEADD('day', 7, d), DATEDIFF('day', start, end), DATENAME('weekday', d), MAKEDATE(y, m, d), DATEPARSE('yyyy-MM-dd', s).

Type conversion: INT, FLOAT, STR, DATE, DATETIME.

Aggregate: SUM, AVG, MEDIAN, COUNT, COUNTD, MIN, MAX, STDEV, VAR, PERCENTILE(x, 0.9), ATTR.

User: USERNAME(), FULLNAME(), ISMEMBEROF('Group').

Level of detail expressions

Text
{ FIXED [Customer] : SUM([Sales]) }        // exactly this level, ignores view and dimension filters
{ INCLUDE [Customer] : SUM([Sales]) }      // view level + Customer
{ EXCLUDE [Region] : SUM([Sales]) }        // view level without Region
{ FIXED : SUM([Sales]) }                   // one value for the whole data source

Table calculations

Text
RUNNING_SUM(SUM([Sales]))        RUNNING_AVG(...)
WINDOW_AVG(SUM([Sales]), -2, 0)  WINDOW_SUM / WINDOW_MAX / WINDOW_MIN
TOTAL(SUM([Sales]))              // grand total in the partition
RANK(SUM([Sales]))               RANK_DENSE   RANK_UNIQUE   RANK_PERCENTILE
LOOKUP(SUM([Sales]), -1)         // previous mark
PREVIOUS_VALUE(0)                FIRST()   LAST()   INDEX()   SIZE()

Quick table calculations (right-click a measure pill): running total, difference, percent difference, percent of total, rank, percentile, moving average, year-to-date total, compound growth rate and year-over-year growth. Always check Compute Using: Table (Across), Table (Down), Pane, or specific dimensions.

Order of operations

  1. Extract filters
  2. Data source filters
  3. Context filters
  4. Sets, conditional and Top N filters
  5. FIXED LOD expressions
  6. Dimension filters
  7. INCLUDE / EXCLUDE LOD expressions
  8. Measure filters
  9. Table calculations and table-calculation filters
  10. Reference lines, trend lines, forecasts and totals

More in Order of Operations and Filter Pitfalls.

Analytics pane

Item Use
Constant / Average line Target or benchmark
Reference line / band / distribution Context for marks (see reference lines)
Box plot Distribution and outliers
Trend line Fitted model (linear, log, exponential, polynomial, power)
Forecast Exponential smoothing with prediction interval
Cluster K-means grouping (clustering)

Keyboard shortcuts (Tableau Desktop on Windows)

Shortcut Action
Ctrl + Z / Ctrl + Y Undo / redo
Ctrl + M New worksheet
Ctrl + E Swap rows and columns
F7 Presentation mode
F5 Refresh data source
Ctrl + drag a pill Copy the pill to another shelf
Esc Cancel a drag

(Shortcuts vary by version and platform. Look under Help > Keyboard Shortcuts in your copy.)

Publishing and security reminders

Performance reminders

Extract with filters and aggregation, fixed dashboard sizes, fewer sheets and filters, simple calculations, fewer marks, and measure with the Performance Recorder (Extracts and Performance Tuning).

Tableau to SQL and pandas

Tableau SQL (SQLite) pandas
SUM([Sales]) SUM(sales) df.sales.sum()
COUNTD([Customer]) COUNT(DISTINCT customer) df.customer.nunique()
IF ... THEN ... END CASE WHEN ... THEN ... END np.where / df.loc
ZN([x]) COALESCE(x, 0) df.x.fillna(0)
DATETRUNC('month', d) strftime('%Y-%m-01', d) d.dt.to_period('M')
{ FIXED [C] : SUM([S]) } window SUM(s) OVER (PARTITION BY c) groupby('c').s.transform('sum')
RUNNING_SUM(SUM([S])) SUM(s) OVER (ORDER BY m) cumsum()
RANK(SUM([S])) RANK() OVER (ORDER BY s DESC) rank(method='min', ascending=False)
TOTAL(SUM([S])) SUM(s) OVER () s / s.sum()
LOOKUP(SUM([S]), -1) LAG(s, 1) OVER (ORDER BY m) shift(1)
WINDOW_AVG(SUM([S]), -1, 0) AVG(s) OVER (ORDER BY m ROWS 1 PRECEDING) rolling(2).mean()
SQL Window functions: the SQL side

Press Run (or Ctrl + Enter) to see the result here.

The same results in pandas, so you can check a table calculation whichever tool you prefer:

Python The pandas side

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. A green pill on Columns makes:

    Green pills are continuous and produce an axis; blue pills are discrete and produce headers.
  2. 2. Which function returns the previous mark's value in a table calculation?

    LOOKUP with offset -1 reads the previous mark, like SQL's LAG and pandas shift(1).
  3. 3. Which of these runs first?

    Extract and data source filters are the first stages of the order of operations.
  4. 4. Which SQL construct matches { FIXED [Customer] : SUM([Sales]) }?

    A window sum partitioned by customer repeats the customer total on every row, like a FIXED LOD.

Summary

  • Blue is discrete, green is continuous; dimensions split, measures aggregate.
  • Remember the families: logical, string, date, aggregate, LOD, table calculations, user functions.
  • FIXED runs before dimension filters; context filters run before Top N and FIXED.
  • SQL window functions and pandas methods reproduce Tableau's table calculations, so you can verify results.
  • Finished the course? Return to the Tableau track page to review any chapter.

Next: Back to the Tableau course