Skip to main content

Table Calculations (Locked)

Table calcs execute after Tableau retrieves the query result set, operating on the visible data grid. R-TC-01 requires partitioning/addressing intent in handoff. R-TC-02 prefers FIXED LOD over table calc when semantics allow.

Window Aggregations​

FunctionSignatureReturnNotes
WINDOW_SUMWINDOW_SUM(expression, [start], [end])NumberSum across window.
WINDOW_AVGWINDOW_AVG(expression, [start], [end])Number
WINDOW_MEDIANWINDOW_MEDIAN(expression, [start], [end])Number
WINDOW_MINWINDOW_MIN(expression, [start], [end])Number
WINDOW_MAXWINDOW_MAX(expression, [start], [end])Number
WINDOW_COUNTWINDOW_COUNT(expression, [start], [end])Integer
WINDOW_STDEVWINDOW_STDEV(expression, [start], [end])Number
WINDOW_VARWINDOW_VAR(expression, [start], [end])Number
WINDOW_PERCENTILEWINDOW_PERCENTILE(expression, percent, [start], [end])Number

Window offset syntax: start and end offsets are relative to current row. Negative = past, positive = future, zero = current.

  • WINDOW_SUM(SUM([Sales]), -89, 0) sums the current row plus previous 89.
  • WINDOW_AVG(SUM([Sales]), FIRST(), LAST()) averages the entire partition.

Running Aggregations​

FunctionSignatureReturnNotes
RUNNING_SUMRUNNING_SUM(expression)NumberCumulative from start of partition.
RUNNING_AVGRUNNING_AVG(expression)Number
RUNNING_MINRUNNING_MIN(expression)Number
RUNNING_MAXRUNNING_MAX(expression)Number
RUNNING_COUNTRUNNING_COUNT(expression)Integer

Rank​

FunctionSignatureReturnNotes
RANK`RANK(expression, ['asc''desc'])`Number
RANK_DENSE`RANK_DENSE(expression, ['asc''desc'])`Number
RANK_MODIFIED`RANK_MODIFIED(expression, ['asc''desc'])`Number
RANK_UNIQUE`RANK_UNIQUE(expression, ['asc''desc'])`Number
RANK_PERCENTILE`RANK_PERCENTILE(expression, ['asc''desc'])`Number

Position​

FunctionSignatureReturnNotes
FIRSTFIRST()IntegerOffset to first row in partition (0 for first row).
LASTLAST()IntegerOffset to last row in partition.
INDEXINDEX()IntegerRow number within partition (1-based).
SIZESIZE()IntegerNumber of rows in partition.
LOOKUPLOOKUP(expression, offset)AnyValue at offset from current row.
PREVIOUS_VALUEPREVIOUS_VALUE(expression)AnyValue of the calc for the prior row.
TOTALTOTAL(agg_expression)NumberAggregates across the entire partition without windowing.

R-TC-01 Partitioning and Addressing​

Table calcs need explicit partitioning intent because default behavior depends on shelf placement. Every table-calc authored via calc-writer must document in the handoff notes:

  • Partition: which dimensions define the group ("compute using" or "partition by").
  • Addressing: which dimension the window traverses ("along" or "advanced").

Example note: "WINDOW_AVG partitioned by Product Category, addressed along Order Date, offset -89 to 0."

R-TC-02 Prefer FIXED LOD Over Table Calc​

If a {FIXED [dim] : agg} would produce the same result as a table calc, use the LOD. Reason: LODs compute earlier in the query pipeline, generally faster and independent of view state.

When to use table calc anyway:

  • Running totals (RUNNING_SUM has no LOD equivalent).
  • LOOKUP / PREVIOUS_VALUE for row-relative reference.
  • WINDOW aggregations with row-relative offsets (not fixable via LOD).
  • Rank calculations.

Common Patterns​

  • 90-day rolling average: WINDOW_AVG(SUM([Sales]), -89, 0).
  • Cumulative sum: RUNNING_SUM(SUM([Sales])).
  • Year-over-year change: (SUM([Sales]) - LOOKUP(SUM([Sales]), -1)) / LOOKUP(SUM([Sales]), -1).
  • Rank within category: RANK(SUM([Sales])) with partition by Category.
  • Percent of total: SUM([Sales]) / TOTAL(SUM([Sales])).

Common Anti-Patterns​

  • Table calc for grand total when TOTAL() or FIXED LOD works.
  • Missing partitioning intent in handoff (R-TC-01 violation).
  • Table calc when LOD suffices (R-TC-02 violation).
  • Nested table calcs > 2 levels deep (readability and perf concern).

Version Notes​

All table calc functions available Desktop 2020.1+.