Our Certified Tableau Trainers are asked a myriad of questions each day. In this series, they share some answers.

Another great question from one of my Advanced classes as posed by Yelda:

“I love box-and-whisker plots, but how do I export that data out? I want to grab the median, the quartiles, everything and put it into a crosstab.”

Awesome question! Let’s find a solution.

bo

Box Plots

As a quick review, box and whisker plots create a box around the second and third quartiles of a range of disaggregated data points. The bottom and top “whiskers” extend to the first and fourth quartiles. Outliers are disregarded in terms of finding that middle 50, situated around the median. Here’s an example of a box plot using sales and product category:

The data that Yelda was looking for is contained in the tooltip whenever I mouse over a component of the box plot:

The problem is that here is no way to bring this data out of the box and whisker plot. Everything you can think of to try (export my data, export to a crosstab, copy my data, duplicate as crosstab, etc.) instead gives you the disaggregated sales for each sub-category like this:

Unfortunately, there is no easy way to grab this like there would be for a trend line or forecast using the Describe option. So, we have to build it.

The Solution

Let’s recreate the box plot and all of its great data in the tooltips instead as a crosstab. We can easily determine the lower quartile, the median, and the upper quartile by changing the aggregation type of each instance of Sales. Do the following for each:

• Median: change the aggregation type to MEDIAN()
• Lower Quartile: change the aggregation type to PERCENTILE and select 25
• Upper Quartile: change the aggregation type to PERCENTILE and select 75

When you add these to your crosstab, your Measures Value card will look like this:

Next, we need to decide if how we want to format the marks outside of the middle 50. The options of a box plot have two different options on how outliers are treated.

Option 1: Calculate Outliers

Anything beyond 1.5 times the interquartile range from the upper and lower quartile is considered an outlier. To calculate the interquartile range (IQR), we take the difference between the upper quartile and the lower quartile. For instance, in the Furniture category the IQR is 483 minus 49 or 434. In that example, the upper and lower whisker should extend from -602 to 1,134.

To accomplish this in our calculated field, we must use a Level of Detail calculation. For the Upper Whisker:

For the Lower Whisker:

If you have any filters on your data, it’s important to add them to context, else the Level of Detail calculations will return results from a broader selection of data than what is in your viz.

Option 2: No Outliers

For the second option, “Maximum extent of the data,” our box plot does not consider outliers. Here’s how the box plot looks like with a tooltip displayed.

The formula to determine the Minimum and the Maximum is quite simple, drag two more Sales measures onto your Measure Values card and change the aggregation for each:

• Maximum: change the aggregation type to MAX()
• Minimum: change the aggregation type to MIN()

Our Measure Values card now looks like this:

Final Viz

Let’s put everything we’ve just done into a single view, including our Upper and Lower Whiskers along with our Min and Max. We’re ready to export our data and keep all of our great box plot information now.

As a final step, we’ll edit our aliases and combine our box plot and our crosstab onto a dashboard for our final viz:

Finito! You can download the TWBX file for this example below.

Thanks to Carl Slifer for his contributions to this article.