Table calculation (Cont.
)
Contents
• An overview of table calculations
• Quick table calculations
• Relative versus fixed
• Scope and direction
• Addressing and partitioning
• Custom table calculations
• Aggregation: Table calculations operate on aggregate data.
You cannot reference a field in a table calculation without
referencing the field as an aggregate.
• Filtering: Regular filters will be applied before table
calculations. This means that table calculations will only be
applied to data returned from the source to the cache. You’ll
need to avoid filtering any data necessary for the table
calculation.
• Table calculation filtering (sometimes called late filtering):
Table calculations used as filters will be applied after the
aggregate results are returned from the data source. The order
is important: row-level and aggregate filters are applied first,
the aggregate data is returned to the cache, and then the table
calculation is applied as a filter that effectively hides data from
the view. This allows some creative approaches to solving
“certain kinds of problems that we’ll consider in some of the
examples later in the chapter
Create table calculations
• There are several ways to create table calculations in Tableau, including:
• Using the drop-down menu for any active field used as a numeric aggregate in the view,
selecting Quick Table Calculation and then the desired calculation type
• Using the drop-down menu for any active field that is used as a numeric aggregate in the
view, selecting Add Table Calculation, then selecting the calculation type and adjusting
any desired settings
• Creating a calculated field and using one or more table calculation functions to write your
own custom table calculations
Quick table calculations
• Quick table calculations are predefined
table calculations that can be applied to
fields used as measures in the view. These
calculations include common and useful
calculations such as Running Total,
Difference, Percent Difference, Percent of
Total, Rank, Percentile, Moving Average,
YTD Total (year-to-date total), Compound
Growth Rate, Year over Year Growth, and
YTD Growth.
• Sales over time is shown. Sales
has been placed on the Rows
shelf twice and the second
SUM(Sales) field has had the
running total quick table
calculation applied. Using the
quick table calculation meant it
was unnecessary to write any
code
Relative vs Fixed
• Relative: The table calculation will be computed relative to
the layout of the table. They might move across or down the
table. Rearranging dimensions in a way that changes the
table will change the table calculation results. As we’ll see,
the key for relative table calculations is scope and direction.
When you set a table calculation to use a relative
computation, it will continue to use the same relative scope
and direction, even if you rearrange the view
• Fixed: The table calculation will be computed using one or
more dimensions. Rearranging those dimensions in the
view will not change the computation of the table
calculation. Here, the scope and direction remain fixed to
one or more dimensions, no matter where they are moved
within the view. When we talk about fixed table calculations,
we’ll focus on the concepts of partitioning and addressing
• Scope: The scope defines the
Scope and direction boundaries within which a given
table calculation can reference
other values.
• Direction: The direction defines
how the table calculation moves
within the scope.
• Scope options: Table, pane, and
cells
• Direction options: Down, across,
down then across, across then
down
Scope and direction
• The table is the entire set of aggregate data.
• The pane is a smaller section of the entire table. Technically, it is defined by the
penultimate level of the table; that is, the next-to-last dimension on the Rows
and/or Columns shelf defines the pane. In the preceding screenshot, you can see
that the intersection of Year on rows and Region on columns defines the panes
(one of eight is highlighted in the view).
• The cell is defined by the lowest level of the table. In this view, the intersection of
one Department within a Region and one Quarter within a Year is a single cell
Scope and direction
• In order to see how scope and direction work together, let’s work through a few
examples. We’ll start by creating our own custom table calculations. Create a new
calculated field named Index containing the code Index()
• Index() is a table calculation function that has an initial value of 1 and increments
by one as it moves in a given direction and within a given scope
Scope and direction
• Create the table with YEAR(Order Date) and QUARTER(Order Date) on Rows and
Region and Department on Columns. Instead of placing Sales in the view, add the
newly created Index field to the Text shelf. Then, experiment using the drop-down
menu on the Index field and select Compute Using to cycle through various scope
and direction combinations. In the following examples, we’ve only kept the East
and West regions and two years, 2015 and 2016
Addressing and partitioning
• Addressing and partitioning are very
similar to scope and direction but
are most often used to describe how
table calculations are computed
with absolute reference to certain
fields in the view. With addressing
and partitioning, you define which
dimensions in the view define the
addressing (direction) and all others
define the partitioning (scope)
• Select Edit table calculation from the
drop-down menu of the Index field
on Text. In the resulting dialog box,
check Department under Specific
Dimensions
