×

Data

# Questions from Tableau Training: How Can I Get Box Plot Data into a Crosstab?

//

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

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:

### The Latest

• ##### Building a Data Culture Through Effective Planning, Governance and Measurement
Easily move to Tableau Cloud with our Tableau Cloud Migration checklist.

### KeepWatch by InterWorks

Whether you need support for one platform or many, our technical experts have you covered.

## Robert Curtis

Managing Director, APAC
Surveys Reveal the 5 Biggest Pain Points for Data & Analytics Leaders InterWorks has been a global, full-stack consulting firm for many years now. A big part of our job as consultants is to first listen to ...
Kickstarting Data Innovation in Healthcare On 13 March 2024, InterWorks was a proud Platinum sponsor of the first ever Data & Analytics in Healthcare conference, hosted by ...

See more from this author →

InterWorks uses cookies to allow us to better understand how the site is used. By continuing to use this site, you consent to this policy. Review Policy OK

×

Interworks GmbH
Ratinger Straße 9
40213 Düsseldorf
Germany
Geschäftsführer: Mel Stephenson

Kontaktaufnahme: markus@interworks.eu
Telefon: +49 (0)211 5408 5301

Amtsgericht Düsseldorf HRB 79752
UstldNr: DE 313 353 072

×