Nov
10
2018

WorkoutWednesday S02 E45: Crosstab highlighting in Tableau

Crosstab or Cross Tabulation is used to aggregate and jointly display the distribution of two or more variables by tabulating their results one against the other in 2-dimensional grids. Cross-tabulation is usually performed on categorical data — data that can be divided into mutually exclusive groups. Cross-tabulations are used to examine relationships within data that may not be readily apparent and if you can highlight a specific column or row in your crosstab it will help you to understand the quantitative relationship between multiple variables.

This post is inspired from Rody’s Recent #workoutwednesday Challenge.

This can be easily achieved via Tableau new Feature – Set Action but before taking that path, let’s follow a conventional method first to solve this Problem.

http://revueplanches.com/evenements/landalou-genie-de-lampe-magique-a-montreal/ Step 1:

To create the initial view, connect to Superstore sample data. Place Region on  go to site Columns and Sub-Category on Rows and SUM(Sales) on Text Marks.

follow url Step 2:

Next to Parameters, click Create, and in the Create Parameter dialog box, complete the following steps

  1. Right-click in the Data pane and select Create Parameter.
    1. In the Create Parameter dialog, do the following, then click OK:
    2. Name: Region Param
  • Data Type: String
  • Allowable Values: List
  • Add from the Field : For value, Select ‘Region’ from the dimension list.
  • Right-click on the parameter and select Show Parameter Control.

2. Repeat the similar Steps for creating ” Sub Categories Param”

Step 3:

Create a Boolean Calculation for your respective Rows and columns which user wants to highlight.

[Region]=[Region Parameter] or [Sub-Category]=[Sub-Category Param]


 

 

Step 4:

Now change the type of mark from Text to Bar. On the Marks card, click the Mark Type drop-down and select Bar from the list.

Step 5:

Create an Adhoc Calculation (Or normal calculated Field) say  AVG (1) and put it on Size Marks and increase it to max value.

NOTE : Ad-hoc calculations are supported on the Rows, Columns, Marks, and Measure Values shelves; they are not supported on the Filters or Pages shelves.

Step 6:

  1. Drag Highlight Rows and Columns onto Color.
  2. Edit the colors so that “False” is White and “True” is gray. Click Assign Palette, and then click OK. Your end result will look like the below image.

Step 7:

Now, lets create another calculated Field which will highlight the INTERSECTION of the Subcategory and Region values.

SUM(IF [Region] = [Region Param]  THEN  1 ELSE 0  END) > 0
AND
SUM(IF [Sub-Category] = [Sub-Category Param] THEN  1 ELSE 0 END) > 0

Add this calculated field on detail marks and then change the Marks from detail to Color by clicking the three dot or ellipses. These dots appear when you have a Dimension pill dropped onto the detail or Tooltip mark. Now put this worksheet on the dashboard and you are done.

Your final dashboard will look like the below image.

Now Time has come to explore the most interesting feature of Tableau i.e Set actions.The Change Set Values action allows you to visually select the values to put in a set. From one or multiple source Worksheets, you can update the values of a set in your Data Source to impact other visualization in NNow your Workbook. Viz interactions just got more powerful with set actions. You can change set members by simply interacting with marks on a viz. It’s a simple but powerful improvement to sets that enables dozens of new use cases like
  • 1) Proportional brushing
  • 2) Dynamic Viz-in-Tooltips with the hierarchical selection
  • 3) Customized highlighting actions
  • 4) Highlight items in the Viz in Tooltip
  • 5) Connected Scatter Plot on hover and many more…
The ability to visually select the values of a set will surely provide great analytical opportunities.
Now, Let’s use this feature to solve our Cross Highlight Problem.
Step 1:
Let’s go through a guided tutorial, using Sample-Superstore available on Data. World website, to see how to configure and use this new action:
a.) Create a worksheet by placing Region on Columns and Sub-Category on Rows and SUM(Sales) on Text Marks.
b.)  You can use Tableau Parameters to Switch Dimensions. Just create a Calculated Field by using a CASE statement for the list of values being used in your parameter. The CASE statement returns the Dimension that matches the value selected in your parameter.
c.) Once you have finished creating your calculated field, locate it in your list of Dimensions and drag it on to the Rows and columns shelf. The parameter drop-down menu should now function properly and switch between the different categories.

Step 2:

Create two sets, X Set and Y Set by right-clicking on the “X-axis Dim” and “Y-axis Dim” field individually and go to Create, then Set. On the Edit Set window, select Use all. The membership is temporary and will be overwritten by the set action. The sets you create will be associated with the data source that is currently selected.

Step 3:

Create a set action by following the below Steps

  1. In a worksheet, select Worksheet >Actions. In a dashboard, select Dashboard >Actions.
  2. In the Actions dialog box, click Add Action and then select Change Set Values.

Step 4:

Configure the action using the same settings as shown in the image below.

As for the Action Filter, you can dictate the behavior when clearing the Action. There are three different behaviors:

  • Keep set value: When you clear the selection, the current values of the Set stays as selected
  • Add all values to set: When you clear the selection, all the values are In the Set
  • Remove all values from set: When you clear the selection, all the values will be Out of the Set

Here, our main purpose is to highlight all values in the same Y/X column as the mark being hovered over.

Click OK to save your changes and return to the view.

Step 5: 

5.1. Now change the type of mark from Text to Bar. On the Marks card, click the Mark Type drop-down and select Bar from the list.

5.2. Create an Adhoc Calculation (Or normal calculated Field) say  AVG (1) and put it on Size Marks and increase it to max value.

 

Step 6: 

Next step is to add X Set and Y Set on color marks and assign the Intersection color as “Red”. As you can see, I have created a copy of X-axis Dim and Y axis Dim and then added these fields( X/Y axis Dim – Header ) to rows and columns (* Refer 1 icon) and Uncheck the header. This is just for creating the border of the cell.

Step 7: 

Test the set action by interacting with the visualization. Tweak some of the formatting settings based on the design behavior as needed. Your final dashboard will look like the below image.

Related Posts

About the Author: Rajeev Pandey

I am a multidisciplinary designer working in data visualization, interaction design and innovation. Expertise in developing Tableau, Web focus based visualization and reporting applications. I have a passion for analyzing, dissecting, and manipulating data sets as well as, building beautiful dashboard. Naturally talented in communicating between technology and business needs. Diverse and experienced in plenty of different domains .I am quick learner who can absorb new ideas and can communicate clearly and effectively.I love creativity and enjoy experimenting with various technologies.

Leave a comment

You must be logged into post a comment.