Construct String Calculations in Tableau

Apr 27, 2020 • 7 Minute Read

Introduction

In business intelligence visualization, we often deal with string data that requires certain functions and calculations. Examples of strings include values represented in text like name, address, and designation. It is important for a Tableau professional to learn this technique, especially the ones dealing with text variables. In this guide, you’ll learn how to perform string calculations in Tableau.

Data

To construct a string calculation, the required data type should be categorical or qualitative. These values are composed of characters and, depending on the scenario, measures can be converted to string format or concatenated with other measures. This guide will be using the sample superstore data available in the Tableau repository.

Baseline

Before constructing string calculations, you’ll create a calculated field. To do this, right click on the Analysis tab and select the Create Calculated Field option. When the calculation window opens, there is a Functions dropdown list that contains the string-specific functions. Select String from the dropdown to get familiar with all the different string functions available on Tableau. This is shown in the image below.

Output:

If you click on any function, the extreme right corner of the calculation window displays information about it, as is shown in the image below.

Output:

String Functions

In the following sections, you’ll implement the most common and extensively used string functions in Tableau.

STR

The STR() function is used in custom calculations, especially to customize the dates or measures that have been defined as integers, but do not represent a numerical variable. The variable, Order Date, is one such example that will be converted to string. To do this, right click on the Order Date, and select Create, followed by Calculated Field, as is shown below.

Output:

The next step is to call the str() function, and pass Order Date as an argument inside the parenthesis.

Output:

The next step is to drag the resulting variable into the Rows shelf to check the output, as shown in the image below.

Output:

Split

The split function helps to split a single categorical value into two or more parts. The function will be applied to the Order ID variable, as it consists of a string values that can be split into two or more parts. The image below represents the variable.

Output:

To perform the split, right click on the Order ID field in the Dimensions shelf, and click on Transform, followed by Split, as shown in the image below.

Output:

The steps above will perform the split, resulting in three parts as shown below. The first part of the split is the string, i.e., the initials like CA. The second and third parts represent the year and number, respectively.

Output:

If you right click on each of these splits, you’ll see the executed calculations behind them. You now know how to perform these splits as calculations. For example, the calculation for first part of the split is shown below.

Output:

The TRIM() function is applied to remove the extra spaces. Apart from the variable Order ID, the split function also uses the arguments - as a delimiter, and a split point for dividing the characters into different tokens.

The calculation for second part of the split is shown below.

Output:

The calculation for third part of the split is shown below.

Output:

You can note that both the second and third calculations use the INT function to convert the value as an integer. The output of the split can be seen below.

Output:

Contains

The CONTAINS() function is useful in scenarios where the requirement is to extract only specific portions of the string value. In this data, you’ll consider the variable Product ID. The objective is to display only those products that have FUR in the product-id. In simple terms, you want to see all the products coming under the furniture category.

To do so, right click on the Product ID variable in the Dimensions shelf. Next, select Create and Calculated Field from the options. The next step is to create a calculated field called Contains Demo using the formula as shown below.

Output:

In the calculation above, you will pass two arguments. The first argument is the Product ID variable as the string, while the second argument is FUR as the substring. Click OK and drag the calculation to the Filter shelf and select TRUE. This is shown in the image below.

Output:

The final step is to bring the Product ID in the view, which will display the following image that contains only the views having FUR.

Output:

Other Functions

There are other commonly used string functions such as Find(), LEFT(), RIGHT() , and REPLACE(). These have not been covered in this guide but the general architecture and steps are the same as discussed above.

Conclusion

In this guide, you learned how to construct a string calculation in Tableau. This will help in strengthening your text analytics capabilities.

To learn more about visualization and data analysis using Tableau, please refer to the following guides: