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.
To create the initial view, connect to Superstore sample data. Place Region on Columns and Sub-Category on Rows and SUM(Sales) on Text Marks.
Next to Parameters, click Create, and in the Create Parameter dialog box, complete the following steps
- Right-click in the Data pane and select Create Parameter.
- In the Create Parameter dialog, do the following, then click OK:
- 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”
Create a Boolean Calculation for your respective Rows and columns which user wants to highlight.
[Region]=[Region Parameter] or [Sub-Category]=[Sub-Category Param]
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.
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.
- Drag Highlight Rows and Columns onto Color.
- 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.
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
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.
- 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…
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.
Create a set action by following the below Steps
- In a worksheet, select Worksheet >Actions. In a dashboard, select Dashboard >Actions.
- In the Actions dialog box, click Add Action and then select Change Set Values.
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.
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.
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.
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.
Leave a comment
You must be logged into post a comment.