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

Performing Simple Linear Regression in Tableau

Today, we will talk about performing simple linear regression in Tableau.  You can see the effects of this by simply turning on trend lines.  However, we don't know of a built-in way to get the equation of that line out of Tableau.  You might ask "Why would I want to know the equation of the line?"  Imagine that you wanted to find out whether a particular point was above or below the line, i.e. above or below average.  If you had the equation of the line, it's nothing more than a simple algebraic exercise (which we won't discuss here).  As usual, we will use the Superstore Sales sample data set in Tableau.

Step 1:

• Create a scatterplot of Profit vs. Sales per Product
• Add a Trend Line to the chart

Profit vs. Sales per Product

Step 2:

• Find the means for Sales and Profit
• Create the following calculated fields

X Mean (Sales)

Y Mean (Profit)

I labelled these X and Y instead of Sales and Profit because it makes it easier to remember where they go in the formulas.  You can name these anything you wish, as long as you know what they are.  Since this procedure is involved, it's always best to drag the values onto the Label Shelf to see if they are calculated properly.

Means (Test)

The means are the same for every point, and they seem to be reasonable values.  This is good enough for us.
Step 3:

• Calculate the difference of each point from the mean for Sales and Profit, individually
• Create the following calculated fields

X Differences

Y Differences

Now, let's check our work.

Differences (Test)

The values are different for each point, and get larger as they get further from the means.  Check #2 complete.

Step 4:

• Calculate the Sum of Squares for XX and XY
• Create the following calculated field

SSxx

SSxy

We won't get into why these formulas are called Sum of Squares, or why they look the way they do.  Just know that these are the mathematically correct formulas.  Now, let's check our work.

Sum of Squares (Test)

We can see that the values are the same for every point, and are really big (which is about all we can decipher from such a value).  Check #3 complete.

Step 5:

• Calculate the Slope and Y Intercept
• Create the following calculated fields

Slope

Y Intercept

Finally, let's check to see if these values are the same as what Tableau calculates.

Slope and Intercept (Test)

Drawing back on high school, you might remember that Slope-Intercept form is y = mx + b.  We can see that our values match the values for the trend line.  Now, we can use these values for any other calculations.  Thanks for reading.  We hope you found this informative.

P.S.

For those of you that remember the Z-Test package we published to the Tableau forums a while ago, it has been updated to include this procedure as well.  We have also created another document describing this technique at http://community.tableausoftware.com/docs/DOC-1479.

Brad Llewellyn

Associate Consultant

Mariner, LLC

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

Creating Fast Navigation Buttons in Tableau

Today, we will talk about how to create fast navigation buttons in Tableau.  In most cases, navigation is quite easy to do by simply using the tabs.  However, if you have a very large workbook or are using a mobile device, such as an iPad, the tabs can be difficult to work with.  Let's look at an alternative.  As usual, we will use the Superstore Sales sample data set in Tableau.

Step 1:

• Create some dashboards

Step 2:

• Create the following text file

Portal Text File

Step 3:

• Create a Tableau Extract using this text file.

Portal Connection

Step 4:

• Connect directly to the extract

Portal Extract Connection

Step 5:

• Create the following calculated field in the "Portal" Data Source

Dashboard 1 Portal

Step 6:

• Drag it onto the Label Shelf of a Blank Sheet
• Format it however you would like

Dashboard 1 Portal Sheet

Step 7:

• Drag it onto Dashboard 2
• Add the following filter action

Dashboard 1 Portal Action

Now you have a navigation button that takes you to another dashboard.  While this method takes up more processing time than using the tabs, it is a useful alternative when tabs are not a viable option.  The main processing issue with method is that the sheet must execute a query and compute the visualization.  However, executing a query against a one-element extract and computing a text table are the fastest possible processes.  We hope you found this informative.  Thanks for reading.

Brad Llewellyn

Associate Consultant

Mariner, LLC

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

Creating Waterfall Charts in Tableau

Today, we will talk about how to create waterfall charts in Tableau.  A waterfall charts is a good tool for seeing how sequential processes contribute to the whole.  It is very similar to a stacked bar, albeit with a different twist.  Here is a sample we pulled from Google.

Sample Waterfall Chart

For this, we created a sample checking account statement in Excel.  Here is a snippet of it.  In case you were wondering, this is fictitious and in no way reflects the statement of ourselves or anyone that we know of.

Row ID	Date	Description	Amount	Daily Posted Balance
2	4/2/2013	Direct Deposit	$485.00	$1,596.56
3	4/2/2013	Rent	($804.74)	$791.82
4	4/4/2013	Food	($16.00)	$775.82
5	4/4/2013	Car	($374.43)	$401.39
6	4/5/2013	Cable	($34.23)	$367.16
7	4/8/2013	Direct Deposit	$1,598.75	$1,965.91
8	4/8/2013	Gas	($39.56)	$1,926.35

Step 1:

• Create a Negative Amount calculation
• Create the following calculation

Negative Amount

Step 2:

• Drag MONTH( [Date] ), DAY( [Date] ) and [Row ID] to the Columns Shelf
• Drag [Daily Posted Balance] to the Rows Shelf
• Drag [Amount] to the Colors Shelf, play with it if you choose
• Drag [-Amount] to the Size Shelf
• Drag [Description] and [Date] to the Details/Tooltip Shelf

Waterfall Chart

Now, we can see a simple representation of our account balance throughout the month.  However, what if your data set doesn't contain a nice [Daily Posted Balance] field.  Have no fear, you can recreate it using table calculations.  Obviously, we would lose the ability to see the start balance, but that's a topic for another post.

Step 1.5:

• Create a Running Sum of Amount
• Create the following calculation

Balance

Step 2:

• Create the same chart as before, using [Balance] instead of [Daily Posted Balance]
• Also, you will need to place [Description] and [Date] on the Tooltip Shelf this time

Waterfall Chart (with Running Sum)

Look familiar?  It should because it is the same exact chart we just saw, except that we now start at 0 instead of ~1100.  Depending on how your data looks you may have to slightly alter the way you approach this.  There are a ton more cases where you can use Waterfall Charts.  We hope you found this informative.  Thanks for reading.

Brad Llewellyn

Associate Consultant

Mariner, LLC

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

Top N% Filters in Tableau

Today, we will talk about Top N% Filters in Tableau.  In our opinion, there are two types of Top N% Filters.  These are showing the top N% of Customers by Profit or the Customers that correspond to the top N% of Sales.  To our knowledge, neither of these features are built-in to Tableau, yet they can be easily achieved using calculated fields.  As usual, we will use the Superstore Sales sample data set in Tableau.

First, we will look at the Top N% of Customers.

Step 1:

• Create a chart of Profit by Customer, sorted by SUM( Profit )

Profit by Customer

Step 2:

• Create a parameter for the N% you want
• Duplicate the parameter
• We will use the 2nd parameter for the 2nd filter

Top N% Parameter

Step 3:

• Calculate the Total Number of Customers
• Create the following calculated field

Total Customers

For those of you who read this blog often, you will recognize this method.  It is an extremely simple method for executing a distinct count.  Basically, it adds 1 for each row of Customer.

Step 4:

• Create a calculated field that returns true for customers less than or equal to the cut-off point.
• Create the following calculated field
• Add it to the filters shelf, filtering on "True"

Top N% Filter 1

Now, let's see if it works.

Profit by Customer (Filter 1)

As you can see, there are a lot less customers in this chart than the original.  You can play with the parameter to make sure it works.  Now, let's work on filtering Customers that correspond to the Top N% of Sales.  You might wonder why we don't just use Profit again.  Since, Profit can, and sometimes will, be negative, you will get funny results if you use this technique on it.  You are welcome to do it on your own to see.

Step 1:

• Create a chart of Sales by Customer, sorted by SUM( Sales )

Sales by Customer

Step 2:

• Calculate the Total Sales
• Create the following calculated field

Total Sales

We could have used WINDOW_SUM() here, just like we did earlier.  However, we'd imagine that WINDOW_SUM() is slower than TOTAL(), so we used TOTAL() here, primarily because this is exactly what TOTAL() is meant for.

Step 3:

• Calculate the Running Sum of Sales
• Create the following calculated field

Running Sum of Sales

Step 4:

• Calculate which Customers are above the threshold
• Create the following calculated field
• Add it to the Filters Shelf, filtering on "True"

Top N% Filter 2

Now, let's see the results.

Sales by Customer (Filter 2)

As you can see, calculated fields are very powerful when it comes to creating custom filters.  I hope you found this informative.  Thanks for reading.

Brad Llewellyn

Associate Consultant

Mariner, LLC

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

Creating Histograms in Tableau

Today, we will talk about how to create histograms in Tableau.  When we say histogram, we are referring to the statistical concept of a histogram.  Basically, this means we are looking at the distribution of values of a continuous variable.  The following is a simple example.  As usual, we will use the Superstore Sales sample data set in Tableau.

Sales by Container

This shows a continuous variable sliced by a discrete dimension.  Despite the fact that some people refer to this as a histogram, we call it a "Bar Chart."

Histogram of Sales

This is what we refer to as a histogram.  It shows the distribution of sales.  Now, we will look at how to create these in Tableau.  We will also show you a trick that we recently discovered for creating histograms at higher grain than the underlying data.

Step 1:

• Right-click [Sales] -> Select "Create Bins..."

Sales (bin)

Step 2:

• Add [Sales (bin)] to the Columns Shelf
• Add [Number of Records] to the Rows Shelf

Histogram of Sales (again)

Now, you have created a histogram and can see that smaller [Sales] are more common than expensive ones.  While this is good, what if you want to look at SUM( [Profit] ) per [Customer]?  To our knowledge, there is no built-in way to do this in Tableau.  Have no fear; there is a way!!!

Step 1:

• Create a [Bin Size] Parameter

Bin Size

Step 2:

• Calculate the bin that corresponds to SUM( [Profit] )
• Create the following calculated field

Sum Profit (bin)

Step 3:

• Calculate a 1 for each member at our new grain (which will be Customer)
• Create the following calculated field

Number of Observations

Step 4:

• Drag [Customer] to the Detail Shelf
• Drag [Sum Profit (bin)] to the Columns Shelf
• Drag [Number of Observations] to the Rows Shelf
• Click on the Colors Shelf -> Change "Border" to "None"

Histogram of Profit per Customer

You may notice something odd about this histogram.  The zero bin seems to have a REALLY large number of values in it.  This is actually because most of our bins are calculated as -200 to -100 or 100 to 200, yet the 0 bin is calculated as -100 to 100.  That's twice as many possible values!!!  Let's fix this.

Step 5:

• Shift the negative profits back one space to create room for the extra bin.
• Create the following calculated field

Sum Profit (bin) (Corrected)

Now, we can test this to see if our hypothesis was correct.

Sum Profit (bin) Test

We were right!!! There were two sets of values in the zero bin.  Now, let's remove [Sum Profit (bin)] from the chart to get our final histogram.

Histogram of Profit per Customer (Corrected)

Now, we can see that most customers are worth between -$500 and $1000.  While creating this method, we did find one interesting flaw in this design.  We could not find a way to calculate the total number of observations in each bin.  We tried using WINDOW_SUM(), but were unable to get it to restart because [Sum Profit (bin)] is not a dimension, but a discrete measure.  If anyone knows any way to get around this, let us know.  We also have another treat.  Remember the Z-Test workbook we created a couple of weeks ago?  We were able to use this technique to add a histogram sheet to that workbook.  Here's a sneak peek.

Z-Test Histograms

The updated Z-Test workbook can be found at http://community.tableausoftware.com/docs/DOC-1429.  We hope you found this informative.  Thanks for reading.

Brad Llewellyn

Associate Consultant

Mariner, LLC

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