Chapter 7: Creating Powerful Calculations

In most analytics scenarios, you'll find that your dataset requires additional calculations in order to perform downstream analysis. For example, you may want to combine the values of multiple fields, or conditionally include certain values only. That's where creating calculated fields comes into play. Calculated fields allow you to perform calculations on your dataset inside Tableau Prep. The benefit of doing so in Tableau Prep rather than in a downstream reporting tool is that Tableau Prep will only need to perform the calculation once. In comparison, if you were to do these calculations in your reports, you'd have to perform them once for each report. Creating a calculation per report requires more effort than creating a calculation once during data preparation. And, of course, if multiple people were to create reports and attempt to calculate a certain value, there would be a risk of applying different calculations, either...

Technical requirements

To follow along with the recipes in this chapter, you will require Tableau Prep Builder.

The recipes in this chapter use sample data files that you can download from the book's GitHub repository: https://github.com/PacktPublishing/Tableau-Prep-Cookbook.

Creating calculated fields

One of the key considerations that is most often overlooked is determining the granularity of the data needed. For example, when working with geographic data, you may have values for the continent, region, country, state, city, ZIP code, street, and so on. But if you only need to report on country data, you may not need all these other dimensions. Or, perhaps you are processing order data; you may want to consider whether you need the details for each individual line item in each individual order, or whether your analysis is satisfied with the total order amount per day. In this recipe, we'll look at a quick method to help reveal the data actually in use in a Tableau Desktop visualization.

Getting ready

To follow along with this recipe, download the Sample Files 7.1 folder from this book's GitHub repository. There, you'll find the December 2016 Sales.xlsx Excel file. In the version of the sales data we've been using throughout this...

Creating conditional calculations

One of the most powerful calculations you can do is conditional calculations. Conditional calculations return a value based on the criteria you set in the calculation itself. When the conditions are met, a certain value is returned, as specified by you in the calculation. Because conditional statements are relatively resource-intensive on your computer's hardware, it is best to perform them during data preparation in Tableau Prep rather than a downstream analysis tool. By performing resource-intensive tasks in Tableau Prep, the data output will already contain the end result, preventing your analysis tools, such as Tableau Desktop, from having to perform these tasks at a less convenient time. In this recipe, we'll calculate the sales tax amount as well. However, this time, we're going to apply a different tax rate based on the department. To do so, we're going to use a conditional calculation.

Getting ready

To follow along...

Extracting substrings

More often than not, data is delivered to us in a less-than-ideal state, with multiple values being held in a single field. We saw how to split data into multiple fields in the Splitting columns with multiple values recipe, in Chapter 3, Cleaning Transformations. However, splitting fields relies on the data being organized, and will never leave out any of the data. In this recipe, we'll look at extracting substrings, which will result in new fields as well. However, unlike splitting fields, we'll be able to more narrowly define what data we want to include in our new field. Furthermore, extracting substrings is non-destructive, that is, the original field will remain unaffected. In this recipe, we'll load a dataset into Tableau Prep that has a field with multiple values in it. We'll then proceed to extract each value and create separate fields for each.

Getting ready

To follow along with this recipe, download the Sample Files 7.3 folder...

Changing date formats with calculations

When you work with many different disparate systems, you're bound to run into a scenario where a date is formatted in such a way that it isn't recognized by Tableau Prep as a date. As a result, Tableau Prep will set the data type for such a field to a string. So, we don't lose any data, but we cannot perform any date functions on such a field. To resolve this, we can create a calculation to re-organize the date string so that the newly added field can be recognized as a date. In this recipe, we'll process a data file using Tableau Prep that holds a date field with values not recognized as a date by Tableau. During the process, we'll change the format of the field using a calculation so that Tableau will then correctly recognize the field as a date data type.

Getting ready

To follow along with this recipe, download the Sample Files 7.4 folder from this book's GitHub repository. There, you'll find the December...

Creating relative temporal calculations

There are many analytics scenarios where you may want to calculate a date-related field based on a relative date, such as today or this year. Such calculations can make your Tableau Prep flow more dynamic and each time the flow is run, Tableau Prep will evaluate the data against the current date or time period. In this recipe, we'll perform a calculation using today's date as a relative anchor. That is, if you execute the flow on July 4, our calculation will use July 4 as a relative point in time. If you execute the same flow the next day, Tableau Prep will automatically adjust to July 5. In this recipe, we'll calculate the age of support tickets for a company's helpdesk, relative to today, that is, how long a support ticket has been open.

Getting ready

To follow along with this recipe, download the Sample Files 7.5 folder from this book's GitHub repository. There, you'll find the Support Requests Extract.csv...

Creating regular expressions in calculations

In this chapter, we've seen how to extract substrings already. In this brief recipe, we're going to explore another method of doing so, using regular expressions. Regular expressions, also referred to as regex, allow you to define a complex search pattern to locate, and in our case extract, substrings. A look at the inner workings of regex is beyond the scope of this book, but a quick web search will reveal numerous sources including example regex statements. In Tableau Prep, you can leverage such statements in a REGEX function.

Getting ready

To follow along with this recipe, download the Sample Files 7.6 folder from this book's GitHub repository. There, you'll find the Missed Chats.csv Excel file. In this file, we find a log of users who have visited our company website and attempted to contact us via live chat when no agent was available to respond. At that point, they submitted their details in a contact form...