Tableau Desktop Cookbook

Chapter 4. Text

The Marks card features many options; one of those is Text. The Text property on the Marks card allows you to display text, which could be labels on a bar, text used to create tables, or text used as a mark type itself. This chapter will take you through some of those features, including tips and tricks for formatting.

4.1 Tables

Problem

You want a text table showing sales by category and year.

Solution

1. 

   Double-click Category.

2. Double-click Order Date.

3. Double-click Sales:

Discussion

A text table can be used in a variety of ways. The main use case is to show the specific values by the dimensions you have used. Tables are also great for sense checking the numbers and calculations when conducting analysis.

If you are using multiple measures, using Show Me to build a quick table might be easier. If you multiselect the fields from the Data pane, Tableau will make a table available in the Show Me menu:

Finally, if you have already created a chart, you can right-click the Sheet tab and duplicate the sheet as a crosstab, which will give you the data in a table format:

4.2 Adding Totals

Problem

You want to see the totals per category, per year, and grand totals.

Solution

1. 

   Choose Analysis from the menu and then select Totals:

2. Choose Show Row Grand Totals:

   

3. To add column grand totals, repeat steps 1 and 2, except choose Show Column Grand Totals.

Your final table should look like this:

Discussion

Totals can be used on almost every chart you create in Tableau. Tableau defaults the aggregation of totals to Automatic, which is based on the aggregation of the field in the view. For example, if you use an average aggregation, your totals will be an average, whereas you might want it to be the sum of the averages. However, you can override the default by choosing Analysis from the menu, then Totals, and then choosing one of the Total All Using options:

You can also add subtotals, depending on the level of detail in your view, which is set by another discrete field in the view. For example, this new table shows Category and Sub-Category sales by year, but you might want to see the subtotals per category:

If you right-click on the Category field on the Rows shelf, you will get the option to add Subtotals:

By default, Tableau puts the Totals and Subtotals to the bottom or right of any chart. We can move these Totals to the top or left by choosing Analysis from the menu, then Totals, and selecting either “Row Totals to Left” or “Column Totals to Top”:

Top Tip 1: Adding Totals from the Analytics Pane

You can also add totals by using the Analytics pane. If you select the Analytics pane on the left, you will see Totals as an option; drag and drop that onto the middle of the view, and this will add both the Column and Row Grand Totals to the chart:

Using the Analytics pane totals allows you to choose either Column Grand Totals or Row Grand Totals. If you had an additional discrete field in the view, you would have the option to add subtotals as well.

4.3 Highlight Tables

Tables are good for seeing the actual numbers, and sometimes you want to make the table more intuitive and effective by adding color to transform your table into a highlight table. As previously mentioned, color is one of the preattentive attributes that allows the user to quickly interpret the chart.

Problem

You want to see the highest and lowest overall profit by subcategory and region by using color and text.

Solution

1. Double-click Sub-Category and double-click Region.

2. 

   Drag Profit to Color on the Marks card:

3. 

   Your current view should look like this:

3. 

   Finally, using Label on the Marks card, check the “Show mark labels” option to turn on the text labels to show the profit values:

Your highlight text table should look like the following:

Discussion

The highlight table shows hotspots or cold spots in your data. For example, the Tables/East cell, which is dark orange, is the least profitable, and the Copiers/West cell, which is dark blue, is the most profitable. The ability to quickly pick out those values was enhanced by the use of color.

The automatic color palette Tableau has used is Orange-Blue Diverging. Diverging color palettes should be used where you have a crossover point, i.e., negatives to positives or above or below a target. If you have only positive or only negative values, you should use a sequential color palette. You can change the colors by clicking Color on the Marks card. The list of available colors depends on the type of values on Color.

Top Tip 2: Custom Color Palettes

You can add your own custom color palettes. You do this inside your Tableau Repository, which is typically located in your documents. The repository contains a file named preference.tps. Right-click it and open it with a text editor. If you haven’t edited this before, you will see just the following text:

    <?xml version='1.0'?>
    <workbook>
    </workbook>

You can add three types of color palettes: regular, ordered-sequential, and ordered-diverging. Each color palette is used for different types of data.

Regular is used for categorical data and can be used with discrete dimensions. Ordered-sequential is mainly used for a single color ranging from light to dark and is available using continuous measures on the Color property of the Marks card. The final type is ordered-diverging, which has two ranges using the color intensity and a midpoint. This type is primarily used for continuous measures and for showcasing a crossover point within the values.

To start adding a color palette, you will need to add a <preferences> tag to your code, between the <workbook> tags:

    <?xml version='1.0'?>
    <workbook>
        <preferences>
        </preferences>
    </workbook>

For every color palette you want to add, you need to open and close a <color-palette> tag. You also need to give it a recognizable and nonambiguous name, as this is the name that will appear when you are choosing colors. The name needs to be followed by one of the three types mentioned. Then another tag is needed for each color you would like in the palette, followed by the hex code of a color, as in this example:

    <?xml version='1.0'?>
    <workbook>
        <preferences>
            <color-palette name = "Name of Palette" type ="regular">
                <color>#000000</color>
                <color>#FFFFFF</color>
                <color>#111111</color>
            </color-palette>
        </preferences>
    </workbook>

Once you have added your custom color palettes, you need to close and reopen Tableau for them to appear in the drop-down list of colors.

4.4 Rank Tables

As mentioned, tables are good for seeing precise numbers. When looking at values, you might want to see where those numbers sit in terms of a rank.

Problem

You want to see how each subcategory ranks for each region.

Solution

1. Build a table with Sub-Category on Rows, Region on Columns, and Profit on Text.

2. To change Profit into a rank, you will need to add a Quick Table Calculation. Right-click the SUM(Profit) field on the Text property. Select Quick Table Calculation > Rank:

   

   

   By default, Tableau computes the rank by region (table across); that is, for every subcategory, it ranks regions based on profit:

3. 

   To change the direction Tableau calculates the rank, right-click SUM(Profit) on the Text property, which now has a triangle to show it is a Table Calculation.

4. Choose Compute Using > Table (down). You should now have a table that shows the rank per subcategory within each region:

   

Discussion

Tableau provides a lot of table calculations. Understanding the Compute Using options depends on the data that is in your view. In the solution just shown, the Table Calculation defaulted to Table (across), which means by region, whereas we wanted to calculate the rank using the subcategory Table (down). However, when using Table (down) or Table (across), moving either of the fields, in Rows or Columns, the Table Calculation will recalculate based on the new layout in the view.

I recommend that once you know what you are computing the calculation by, you should change the calculation from the default settings. To do this, go into the Edit Table Calculation option and choose the Specific Dimensions option. This means if you change the layout of the view, the Rank calculation will still compute using Sub-Category, as long as Sub-Category is still in the view.

If you remove Sub-Category from the view, you will get a red field, indicating that something has happened with the Table Calculation:

Tableau has four rank options, which change how it ranks the values. We can use the values 100, 95, 95, and 85 to demonstrate the differences:

• Competition rank would give the two 95 values the same higher rank of 2 and give 85 the rank of 4.

• Modified Competition rank would give the two 95 values the same lower rank value of 3, and 85 the rank of 4.

• Dense rank would give the two 95 values the higher rank but would give 85 the next rank value of 3.

• Unique rank would give each value a new rank depending on how the data is sorted, but by default it is alphabetically.

  

You can also create a rank calculation by using the calculated field option, where you can see the description for the calculation:

When creating a Table Calculation through a calculated field, you will notice “Default Table Calculation” at the bottom right:

This allows you to specify how you want your Table Calculation to compute. When you click this option, it brings up the Calculation Definition:

This Calculation Definition allows you to set your Compute Using options. However, when you change the default, if you don’t have that field in the view when using the calculation, the Rank Calculation field will error.

For more information about Table Calculations, see Chapter 16.

4.5 Big Actual Numbers

Big Actual Numbers (BANs) are used on dashboards (see Chapter 7) to give the viewer high-level, most-critical information immediately. BANs are usually key performance indicators (KPIs) for the business.

Problem

You want to have a BAN for the latest year of sales so far.

Solution

1. 

   Drag Sales to Text on the Marks card:

2. 

   Drag Order Date to Filters and select Years:

3. Check the box at the bottom, “Filter to latest date value when workbook is opened”:

   

This then shows the sales value for the latest year of data:

If you click Text on the Marks card, you can change the alignment to Center, which allows the BAN to be in the center:

Discussion

BANs are used for the high-level, most critical KPIs on a dashboard. They can be formatted depending on the dashboard. You can increase the size by using Size on the Marks card or increase the font size in the Text properties. You can also change the color of the BAN, which can be done in one of three ways:

1. Use Color on the Marks card and use the default colors or a color picker.

2. Use a color that is in the color palettes. Create a calculated field with any text or letter—this is just a dummy calculation for the color and then add that calculation to Color on the Marks card.

   

3. 

   Change the color of the font inside the Text properties. Click Text on the Marks card and select the three dots. You can then highlight the text and change the color:

4.6 Calculating Percent Difference

When using BANs, you might want to include a percent change from another value (a previous value or time period, for example). This section will show you how to calculate the percent difference, and Recipe 4.7 will show you how to keep only one value.

Problem

You want to see the percent difference for sales from the previous month.

Solution

1. Filter to the latest year (see Recipe 4.5).

2. Right-click (Option on Mac) and drag Order Date to the Rows shelf and choose MONTH. Then add Sales to Text on the Marks card:

   

3. The goal is to calculate the percent difference from the previous month in the view. Right-click SUM(Sales); then choose Quick Table Calculation > Percent Difference:

   

Note

Always double-check how your Table Calculation is being computed. Also, I highly recommend changing from the default to Specific Dimensions when you are happy with the compute.

This now shows the percent difference for sales compared to the previous month:

Discussion

The percent difference calculation has the same Compute Using options as previously mentioned in Recipe 4.4. The default this time is Table (down), which is computing by month, because month is the only field in the view. Ensure the Specific Dimensions option is selected. January is blank because it doesn’t have any previous values to compare to (i.e., the year filter has removed the option to compare against December of the previous year). See Recipe 4.7.

Currently the default option compares the values to the previous value; however, we can change this option under “Relative to.” This means you can select which month you want to compare to. Currently, the default is Previous (month), but you can select Next to look at a future month, First to look at the first month (January), or Last to show the last month (December).

If you want to show the actual difference between the months, add another SUM(Sales) to Text on the Marks card, and then right-click and select Quick Table Calculation > Difference. You will need to change the “Relative to” option if you have changed the percent difference from the default.

Tip

If you drag the new sales field, with the triangle on, to the left toward the Data pane, Tableau will save the Table Calculation as a calculated field. You can then reuse the same calculation across your worksheets. You might need to change the Compute Using option when you drag it into a new sheet.

If you press Ctrl (Command on Mac) and click to drag a field to another part of the view (i.e., the Filters shelf), the Table Calculation will also travel with the field.

In the next section, we will finalize the BAN by showing only the last row of data.

4.7 Using LAST and Hide

In Tableau’s order of operations, table calculations are computed after every filter, meaning that if you filter a year or a month, you will lose the percent difference calculation, like the following:

This recipe uses a table calculation called LAST that can be used to hide data.

Problem

You want to see only the LAST month as a BAN while also keeping the percent difference from the previous month.

Solution

1. Re-create or duplicate Recipe 4.6.

2. Create a new calculated field and type LAST()=0:

   

3. Drag this calculation to the Rows shelf. Notice that you now see True or False. December = True because that is the last value using the default Compute Using of Table (down).

4. 

   Right-click the word False and click Hide:

This then keeps just the original percent difference and difference from previous values:

Discussion

In Tableau’s order of operations, table calculations are computed last, after all the filters. By using the LAST table calculation and hiding the false results, you can still keep the table calculations for your BANs and show only the latest month in the dashboard.

The LAST table calculation gives each row in your view a number that indicates the distance from 0. In this example, since December is the last value in the table, it would be assigned the value 0. November would be given the value of 1 because you need to go forward one month to get to December.

If you want to keep the first record in your view, the opposite of the LAST function is using FIRST, where the same principle applies—the first value gets the number 0. However, the other values start with a negative, indicating the number of marks you have to go backward to get to the first mark.

In Recipe 4.6, I mentioned that January would be blank since it doesn’t have a value to compare against. You can use the FIRST function to hide the first value in the list.

4.8 Custom Number Format

Problem

When computing a difference or percent difference, you want to apply a custom format to the text, parentheses to indicate a negative difference, and an upward or downward triangle for a positive or negative percent difference, respectively.

Solution

1. Start by either duplicating Recipe 4.7 or creating a new sheet with a percent difference calculation.

2. 

   Right-click the Value Difference table calculation and click Format:

3. Select the Numbers drop-down. Here we can select a variety of options. For the value difference, we want to change it to a currency, have 0 decimal places, and change the negative values to be in parentheses:

   

4. Next, you want to change the percentage format. Repeating the same process as before, right-click the Percent Difference Table Calculation.

5. This time in the Numbers format, select Percentage and then select Custom.

6. 

   In this box, you now need to tell Tableau what you want to do with the positives, negatives, and neutrals. Positives and negatives use upward and downward arrows. Use the following text: ▲0.0%;▼0.0%;0.0%.

Note

This is used as an alternative method if you want to customize the number even further.

Codes for the up and down triangles are Alt + 30 and Alt + 31

Your final view should look like this:

Discussion

Custom formatting is an extremely useful key concept because you can customize each measure with a different number format. If you change the format of a field in the view, it will impact only that visualization. If you want to use that format on the measure in different views, you will need to change the default format of a measure, which means whenever you use that measure, it will use the same formatting. However, view-level formatting can overwrite the default formats. To do that, you will need to right-click a measure in the Measures section of the Data pane and select Default Properties. Selecting the Number Format gives you the same options as mentioned earlier.

When doing the custom formatting, notice how you use semicolons to separate the figures. This is to differentiate positive values, negative values, and zero values.

4.9 How to Zero Nulls

The zero null (ZN) function can be wrapped around any calculated field to convert nulls to zeros.

Problem

When showing a full year difference or percent difference, you want the first value to show 0 value and 0% instead of null.

Solution

1. Open a value difference calculation (see Recipe 4.6).

2. At the start of the calculation, write ZN (and at the end of the whole calculation, close the parenthesis, and then click OK):

   

3. Repeat for the percent difference sales:

   

Now the January figures show zero instead of nulls, like the following:

Discussion

The ZN function is especially helpful when you have null values in your data. Having a zero instead of a null is important when creating calculations; you have to be sure that you can treat nulls as zeros before you can use this calculation. The calculation window also describes what the ZN calculation does:

You might also notice that when you converted the table calculations into reusable calculations, Tableau used ZN inside those calculated fields. If any value is a null, the calculation would return a null, which is why it is important to use the ZN function, to convert any nulls to zeros by default.

To use the ZN function, a row of data needs to exist to replace the null with a zero.

4.10 Showing Positive, Negative, or Neutral Values

Problem

You want to color the percent difference based on positive, negative, and neutral values.

Solution

1. 

   Create a new calculated field using the SIGN function and Percent Difference Sales field:

2. 

   Right-click this new calculation and select Convert to Discrete:

3. 

   Add the calculation to Color on the Marks card and edit the colors. I chose to use orange for negative, gray for neutral, and blue for positive:

Your final view should look like this:

Discussion

The SIGN calculation assigns a positive value to the number 1, neutral values to 0, and negative values to –1. Prior to learning this SIGN function, I always used to create conditional IF statements. Using the SIGN function reduces the need to write long IF statements. I learned this from Ann Jackson, a Tableau Zen Master, on one of her speed tipping sessions.

See Also

Ann Jackson and Luke Stanke, Tableau Strategies (O’Reilly)

4.11 Calculating a Good, OK, or Bad Status

Using SIGN is useful for positive, neutral, and negative values. But what if you want to see whether a percentage value is good, OK, or bad? These statuses are used in businesses and project management to identify performance or the status of a project.

Problem

You want a status for the percent difference calculations, using Good for greater than 50%, OK for between 0% and 50%, and anything Bad for less than 0%.

Solution

1. Filter to the latest year of data. Create a view with Month of Order date on the Rows shelf and percent difference sales on Text.

2. Create a new calculation and use the following IF statement:

   

3. Add this new calculation to Color on the Marks card and edit the colors to indicate Good, OK, or Bad:

   

Discussion

These types of statuses are used in project management and are seen as a traffic light system. They are good for visualizing when values are above or below a certain target or percentage. When using this type of status, you might want to be careful with the colors. If people viewing your dashboards are color-blind, they might not be able to see the differences based on the colors. Instead, Tableau has a Color Blind palette that uses shades of orange, blue, and gray:

Having this type of status on BANs allows the user to instantly see whether that particular number is above or below the condition that has been set.

See Also

IF statements are mentioned in “Conditional Grouping”.

4.12 Using Titles as BANs

Problem

You want a single BAN without a tooltip or clickable action.

Solution

1. 

   Right-click (Option-click on Mac) and drag Order ID to Detail; select Count Distinct [CNTD(Order ID)]:

2. Double-click your title at the top. Delete <Sheet Name>, click Insert, and select CNTD(Order ID). This adds the Count Distinct of order IDs to the title.

   

3. 

   Finally, change your Marks card to a Polygon to remove the text in the middle:

Your BAN should now look like this:

Discussion

When using the Title elements as BANs instead of Text, there is a better user experience when you build dashboards in Chapter 7. Titles as BANs are good if you have a single number and don’t want conditional formatting with Good, Bad, and OK indicators, and if you don’t want or need a tooltip on this specific value. This way also fills an automatic space better without having to use a fake axis calculation to get the space filled correctly. However, if you need to add another dimension, like a Good, Bad, and OK indicator, you will be better using a normal BAN (see Recipe 4.5).

4.13 Using SIZE

Problem

You want to count how many marks are in your view.

Solution

1. Add Order ID to Detail on the Marks card.

2. 

   Create a new calculated field with the following syntax:

3. Add the Size field, just created, to Detail on the Marks card. You will need to compute using Order ID in this case.

4. Add this calculation to the title and change the mark type to Polygon to create a Title as a BAN, as in Recipe 4.12:

   

Discussion

Size can be used as an alternative to Count or Count Distinct. The difference is that it counts whatever details you put in your view and how you compute. The Tableau description is as follows:

If you added another level of detail to your view, the SIZE value would change. For example, adding the product name changes the title to show 1 to 48:

If we change the Compute Using option to Order ID and Product Name, you’ll notice that the title is None:

That is because you have changed the Compute Using option, and when you edit the title, it says Missing Field. You will need to re-add the calculation to the title:

When you do, you’ll notice the value has increased to over nine thoousand. That is because there is more than one product name to a single Order ID and Tableau is therefore counting the total number of products within the total number of orders.

4.14 Word Cloud

Word clouds are a visual representation of text in your data, with the option of adding context to those words.

Problem

You want to see the subcategories colored and sized by total sales.

Solution

1. Drag Sub-Category to Text on the Marks card.

2. 

   Change the mark type from Automatic to Text:

3. Add Sales to both the Size and Color properties.

Your final output should look something like the following:

Tip

If you press Ctrl, then select and drag a field, you can duplicate this field to any shelf within the workspace.

Discussion

Word clouds have a good visual appeal when looking at text data, and users can quickly see the most frequent words. However, word clouds have some weaknesses. It is difficult to compare the size and color of each word in the view. The layout of the word cloud is also dependent on the word length; if the word has 20 characters, it is naturally going to stand out more than a word with only 5 characters. Word clouds can also have a messy arrangement, and you cannot sort the words by a particular field. The alternatives for this type of chart are a bar chart (see Recipe 3.1) or a treemap (see Recipe 8.1).

Summary

You can incorporate text into data visualizationsin many ways, by using BANs or labels. Text tables are also a fundamental part of Tableau, especially for those trying to switch from using Excel to creating visuals in Tableau.