Sets vs T|F Calculated fields

  1. Sets are similar to a True|False calculated field that identifies records that are IN vs OUT of the selected criteria. The same results can often be accomplished simply using T|F filter calculations.

  2. Some significant differences between T|F calculated fields and Sets are:

    1. With Sets, you can dynamically select which records are in or out of the Set. You can’t do that easily with a T|F Calculated field.

    2. Set logic processes before any dimension filters process, like FIXED LOD calculated fields, whereas T|F calculated fields process after dimension filters.

  3. Sets have more functions available in the Tableau Desktop app, but they are still a useful tool in the Web App.

Sets vs Groups

Sets & Groups have similar characteristics in that they can be used to group records. Here are some of the differences:

  • Sets allow the Viewer to select which members belong in a set. To edit the members of a Group you must edit the workbook.

  • Groups can have different values within the one group.
    i.e., salespeople can be grouped into different territory names within the one Territory group. But a set only has two states of IN or OUT of the Set.
    There is no Set function to say Salesperson1 is in the Western Territory and Salesperson2 is in the Eastern Territory. A similar result is possible by creating a separate Salesperson Set for each Territory and then creating a conditional Territory calculated field.

  • In Groups, a record can only be a member of one group value.
    i.e., Salesperson1 cannot be a member of the Western Territory and the Eastern Territory groups.
    With Sets, you can make a record of multiple sets. Salesperson1 can be a member of the Western Territory Set and the Eastern Territory set.

The most significant differentiator for Sets is the ability to dynamically select which records are in the Set.

Using Sets in the Tableau Web App

A common use of Sets is to dynamically select records to be in the Set so a record shows as IN or OUT.

Task: Create a new worksheet with Customer Set, Customer and Sales. Then use the Show Set Control to dynamically select which customers are IN or OUT of the Customer Set, showing subtotals for each set group. See an example at 02 Sets: 01 Show Set Control - DataSelf Analytics

  1. The first step is to create a Set for the Customer field.
    Right click on the Customer field and select Create > Set.

  2. Once you have created a Set, there are three methods to select the records for your set.

    1. The Shortcut Menu has options to create a set, add to set, remove from set.

      Shortcut menu set options

    2. Right click on the Customer Set field and select the Show Set option to display the Customer selection list on the right. The Tableau help link is: https://help.tableau.com/current/pro/desktop/en-us/sortgroup_sets_create.htm#SetControl
      NOTE: The Set field needs to be on the worksheet in some manner in order to show it. You might mark it as a detail, color, or tooltip to have it on the worksheet.

      Select the Show Set property to select members from a list


      You can also check Show Filter on the Set field to select records in the set or out of the set.
      DataSelf Example: Example 01 Show Set Control - DataSelf Analytics

    3. Using Worksheet Actions enable you to dynamically choose the members of a set by clicking/highlighting rows. Tableau help link: Set Actions - Tableau
      DataSelf Example: Example 02 Worksheet Set Actions

      1. Create your Set. In this scenario we are using the Customer Set previously created.

      2. Click on the Worksheet Menu and select Actions topic.

      3. Add an Action to Change Set Values when selecting the Customer Set field on the worksheet.

Click on the Worksheet Menu, Actions

Add action to Change Set Values

Choose the action options

3. In the example below, 02 Sets: 02 Worksheet Set Actions - DataSelf Analytics, the Customer Set field is added to the Rows. The standard field label is IN/OUT(Customer). The customers are then sorted by sales in descending order (highest to lowest). Clicking on the first 5 customer records adds them to the IN group of the Set, based on the worksheet action settings. This shows the top 5 customers based on sales in the year. To clear the set selection, click twice on the IN label to first add all records then clear all records.

The Set % GT measure showing the subtotal sales per set as a % of the sales grand total is explained in the next section.

Using LODs to show the Customer Set subtotal as a percent of grand total Set % GT.

  1. Create an LOD calculated field to show the Sum (Sales) per Customer Set output (IN/OUT)

    LOD (including YEAR) to show sum of sales per Customer Set

  2. Create another LOD calculation to show the grand total of sales per year.

    LOD (including YEAR) to show grand total of sales

  3. Create a regular calculated field to show sales per Customer Set / Sales Grand Total

    Calculated field Sales per customer set / sales grand total percent

Using Sets for selective drill-down

With the hierarchy drill-down function, when you expand a hierarchy, it does it for all the records, not just the one highlighted. But you can use Sets to create a drill-down feature that expands only the highlighted record.

DataSelf worksheet link: Example 03 Sets for Drilldown

In this example the Item Class field is displayed on the worksheet. The goal is to double click on a specific Item Class and list all the items in that Class.

  1. Create a Set for Item Class. No Item Classes have been selected to be in the Set yet.

  2. Add the Item Class Dimension to the Rows section of the worksheet.

  3. Create a Calculated field with the formula If [Item Class Set] Then [Item] Else '' END
    This formula states if an item is in the selected Item Class, then show the Item, otherwise display a blank.
    In this example the calculated field was named Item Class - Item.

  4. Add the Item Class-Item field to the rows section of the worksheet.

  5. Create a Worksheet/Action to Change Set Values

Create a Change Set Values worksheet action

Set Actions will Assign values to set. Clearing will remove values

6. When an Item Class is click/selected, it is added to the Item Class Set. Then the Item Class-Item calculated field displays all the Items in the set.

When click on CONSUMER 100BABY that Dimension value is added to the Item Class Set

The Item Class-Item calculated field displays all Items in the selected CONSUMER 100BABAY Item Class Set.

Use Sets instead of Groups for dynamic Sales territory assignments by viewers.

02 Sets: Example 04 Sets as Territories - DataSelf Analytics

  1. Create a set from the Salesperson field for each Territory

  2. Rename the sets with the Territory names

  3. Select Show Set for each of the sets so the viewer has a selection list of salespeople for each territory.

  4. The advantage of this is that the viewer can select the salespersons for each territory without needing to be an editor to modify the workbook.

Use Multiple Sets to identify records in all the sets.

02 Sets: 05 Dashboard Cust/Item Sets - DataSelf Analytics

  1. Create a worksheet with Customer Sales sorted in descending order so you see the top sellers at the top
    02 Sets: 05a Customer Ranked Set - DataSelf Analytics

    1. Create a Customer Set & Worksheet action to add records to the set

  2. Create a 2nd worksheet with Item Sales sorted in descending order so you see the top sellers at the top
    02 Sets: 05b Item Ranked Set - DataSelf Analytics

    1. Create an Item Set & Worksheet action to add records to the set

  3. Create a 3rd worksheet with In/Out Customer Set and In/Out Item Set

  4. Create a 4th worksheet with In/Out Item Set and In/Out Customer Set

  5. Show the Customer & Item Set filters and set them to only In

  6. Create a Dashboard showing all 4 worksheets
    02 Sets: 05 Dashboard Cust/Item Sets - DataSelf Analytics

  7. As you click on top Customers and Items to add them to their sets, the combined worksheet will show the records that meet both conditions.