Introduction to Table Calculations: Compute Using

Today, we will talk about basic table calculations.  More specifically, we will talk about the Compute Using feature.  We believe that this is the most important, yet most undervalued, feature of table calculations.  In fact, almost all of the table calculation posts on the forums are handled with a simple use of Compute Using.  As usual, we will use the Superstore Sales sample data set in Tableau.

Step 1:

• Create a calculation that returns 1 for every cell
• Create the following calculated field

1

Step 2:

• Create a calculation that counts 1, 2, 3, etc. through the cells
• Create the following calculated field
• Duplicate this field a few times

Running Sum of 1

A more advanced user might recognize that this is what the INDEX() function is designed to do.  We agree entirely; but, we felt it was simpler to understand using a running sum, which most people should already know.

Step 3:

• Drag YEAR( [Order Date] ) onto the Rows Shelf.
• Drag 2 of the Running Sum fields onto the the chart as text.

Table (Down)

As you can see, the default direction is Table (Down).  This means that the table calculation will continue to move down the table until it reaches the end, never restarting.

Step 4:

• Change the Compute Using on one of the Running Sums to use Cell

Compute Using Cell

Cell

As you can see, Cell refers to the current row.  In fact, Table Calculations at the cell level are not very useful, except in extremely rare cases.

Step 5:

• Drag MONTH( [Order Date] ) onto the Rows Shelf as well.
• Add another copy of Running Sum to the chart, with Compute Using: Pane (Down)

Pane (Down)

Now, we can see that Table (Down) still runs the entire table and Cell runs nowhere.  The point to notice here is Pane (Down) restarts every year.  More specifically, the Pane is area between the table and the cell.  Here is a good illustration of the concept.

2 Dimension Illustration

Now, you might question something.  What happens if there are more than dimensions?  Which one is the Pane?  Let's take a look

Step 6:

• Add QUARTER( [Order Date] ) to the Rows Shelf between Year and Month

3 Dimensions

As you can see, the Pane restarts every Quarter, not every Year.  This means that the Pane is the area just larger than the Cell, regardless of the number of dimensions in the Table.  The following illustration should help.

3 Dimension Illustration

We don't know what to call the area(s) between the Table and the Pane.  If you want to work in this area, you will have to get more explicit with your Compute Using.  We will explain this in a later post.  Lastly, let's look at directions.

Step 7:

• Create a blank chart
• Place Year on the Rows Shelf
• Place Quarter on the Columns Shelf
• Place 4 copies of Running Sum on the chart as text
• Set each copy to use a different direction for Compute Using: Table, i.e (Down), (Across), etc.

Directions

As you can see, Table (Across) goes across the columns, then resets at every row.  Table (Down) does the opposite.  Table (Across then Down) goes across the columns, but doesn't restart at a new row.  Table (Down then Across) does the opposite.

This blog post will accompany a webinar that goes over this material with some more examples.  The webinar is free and anyone in Charlotte, NC is welcome to attend live.  The webinar should also be aired live and posted on YouTube.  I will relay the specifics of this on my LinkedIn and the Tableau group on LinkedIn when I have them.  Thanks for reading.  I hope you found this informative.

Brad Llewellyn

Associate Consultant

Mariner, LLC

llewellyn.wb@gmail.com
https://www.linkedin.com/in/bradllewellyn