Last Modified Date: 19 Aug 2019
Option 1: Use table calculations in Tableau Deskop
Step 1: Create the Calculations
- Create a calculated field with a name like "Create the list" with a calculation similar to the following:
IF FIRST()= 0
ELSE PREVIOUS_VALUE("") + ", " + MIN([Sub-Category])
- Create a calculated field with a name like "Sub-Category List Label" with a calculation similar to the following:
WINDOW_MAX([Create the list])
Edit calculated fields in the attached workbook for additional notes about how these calculations work.
Step 2: Create the Bar Chart View
- Drag [Category] and [Sub-Category] to the Rows shelf
- Drag [Sales] to the Columns shelf
- Drag [Sub-Category] to Color on the marks card
Note: [Sub-Category] must be included in the view for the calculations to work correctly. If the final view should not include the dimension being listed in the tooltip, see the "Adv Option 1" tab for additional instructions
- Drag [Sub-Category List] to Tooltip on the marks card
- Right-click [Sub-Category List] on Tooltip and select Compute Using > Sub-Category
Option 2: Use Tableau Prep
- Add a step
- Right-click [Sub-Category] in Clean 1 and select Duplicate Field
- Add Aggregate
- In Aggregate 1, do the following:
- Add Category to Grouped Fields
- Find Sub-Category in the list of Additional Fields, click GROUP, and select Minimum (This will add MIN(Sub-Category) to Aggregated Fields)
- Right-click MIN(Sub-Category) in Aggregated Fields and select Rename. Rename it something like "Sub-Category (first)"
- Add MAX(Sub-Category-1) to Aggregated Fields
- Rename MAX(Sub-Category-1) to something like "Sub-Category (last)"
- Drag Aggregate 1 over Clean 1 and drop Aggregate 1 on Join
- Add a step from Join 1
- In Clean 2, create a calculated field named "Filtered Sub-Category" with a formula like
IF [Sub-Category] != [Sub-Category (first)] AND [Sub-Category] != [Sub-Category (last)] THEN [Sub-Category] END
- In Clean 2, duplicate [Filtered Sub-Category]
- In Clean 2, remove [Category-1] and [Sub-Category]
- Repeat steps 3-9 to create as many fields as needed. In this example, the category with the most sub-categories has 9 sub-catgories, so the prep flow needs 5 aggregate steps to create 10 fields so no sub-categories are missed.
- In the last Clean step, create a calculated field named something like "Grouped sub-categories" with a formula like:
IF ISNULL([Sub-Category (first)]) THEN '' ELSE [Sub-Category (first)] + ' ' END + IF ISNULL([Sub-Category (second)]) THEN '' ELSE [Sub-Category (second)] + ' ' END + IF ISNULL([sub-category (third)]) THEN '' ELSE [sub-category (third)] + ' ' END + IF ISNULL([sub-category (third to last)]) THEN '' ELSE [sub-category (third)] + ' ' END + IF ISNULL([Sub-Category (second to last)]) THEN '' ELSE [Sub-Category (second to last)] + ' ' END + IF ISNULL([Sub-Category (last)]) THEN '' ELSE [Sub-Category (last)] + ' ' END
The above calculation combines all of the sub-categories fields made. If the sub-category field has a NULL value, then that NULL must be replaced with an empty string (aka two quote marks '') because combining a value and NULL value always results in NULL.
- Output the data
Additional InformationTo voice your support for the inclusion of this enhancement in a future product release, add your vote to the following Community Idea: Concatenate field values across rows into comma-separated list.
Discuss this article...
Thank you for providing your feedback on the effectiveness of the article.
Open new Case
Training and Tutorials