Creating awesome calculated fields in Tableau

What are calculated fields?

Calculated fields in Tableau allow you to add user-defined expressions as new data fields. These new data fields only exist in the Tableau workbook and do not impact the original data source. As Tableau visualization builders, we can use calculated fields to do the following;

• Create aggregated fields
• Concatenate fields of different types (add a number to a string)
• Build out logical aliases
• Create if/then statements
• Split fields on different characters
• Change the formatting of fields
• Use regex to extract parts of fields
  

This list is just a tiny amount of the functionality that we can get out of calculated fields. They really are powerful, and if you are not already using them, you are limiting the potential for your visualization. Next, let’s look at some of the benefits of calculated fields.

The Benefits of using calculated fields

One of the best things I have found about calculated fields is that once you know where to go to create them, the learning curve to leveraging them is not steep. The Tableau interface for building out calculated fields gives some great examples of using each of the different functions that can be used right in the builder. It is worth exploring this built-in documentation to generate some ideas on how you might be able to use the calculated fields to start customizing your data.

Another benefit of the builder is that it warns you when your calculated field is invalid. It provides some good explanations for correcting your mistakes so that your calculated field can function correctly. I find this functionality very valuable when I am building out complex calculated fields, and I make a small typo (missing “,”) while typing.

As I have already mentioned. When you are building calculated fields, you will not be impacting your original data source, so you can play around with them and not worry about making mistakes. I love this because I really enjoy experimenting with data. I don’t have to worry about something breaking when I am doing this. I can easily step away from the work without needing to undo anything, and it will still be there for me to continue experimenting with when I open the workbook back up another time or day.

I love that you can build calculated fields from other calculated fields. This is amazing, considering we are not adding any fields to the original data source. I have found myself using this functionality quite a bit when I am working with regular expressions. I work with a lot of data fields where a code like ‘mpmfi’ has several layers of meaning built into the code. In this example, the first two characters point to an organization, and the third character points to a specific branch of that organization.

Using nested calculated fields, I can extract the first three characters to build out the branch and then use that calculated field to extract the first two to get the organization. Technically, I could build the organization calculated field using the full code, but I figure that because there is more data to load, I can actually decrease the load it takes to calculate the field when it has fewer characters.

Some of my favorite calculated fields

I have been using calculated fields for several years and now have a pretty large list of visualizations and reports. I work with Public, Academic, and School libraries and support their patron and circulation databases. I rarely create a new report without using a calculated field of some sort. These calculated fields range from pretty simple to highly complex. Below is a list of some of my favorites that I have built over the years and their purpose.

Create Bibliographic call number range reports

The Ask:

The member library wanted to see a report showing the circulation of their materials based on call number ranges. No existing reports did this. However, we do have access to call numbers in the items database view.

The calculated field:

IF STARTSWITH([Call #s],"27") THEN "270-279.999" ELSEIF
STARTSWITH([Call #s],"28") THEN "280-289.999" ELSEIF
STARTSWITH([Call #s],"29") THEN "290-299.999" ELSEIF
STARTSWITH([Call #s],"30") THEN "300-309.999" ELSEIF
STARTSWITH([Call #s],"31") THEN "310-319.999" ELSEIF
STARTSWITH([Call #s],"32") THEN "320-329.999" ELSEIF
STARTSWITH([Call #s],"33") THEN "330-339.999" ELSEIF
STARTSWITH([Call #s],"34") THEN "340-349.999" ELSEIF
STARTSWITH([Call #s],"35") THEN "350-359.999" ELSEIF
STARTSWITH([Call #s],"36") THEN "360-369.999" ELSEIF
ELSE "Other Call #s" END

The above is a subset of the call field. It matches the beginning of the field and uses that data to create the groups. Below is a screenshot of what the report looks like.

Extract a transit date from a string of text

The Ask:

I was asked to generate a report that could show items that had been in transit between libraries for too long. This report would help libraries identify materials that had potentially been lost in the courier system. The transit dates are contained in the text field that looks something like the following;

May 25 2017 09:12AM: IN TRANSIT from cmscirc to mpm

We wanted to be able to use the date field as a filter for a date slider. Strings do not work well in this kind of functionality.

The calculated field

DATE(DATEPARSE ( "MMM dd yyyy", REGEXP_EXTRACT_NTH([TransitText], '(^.{11}+)\s.*', 1) ))

The above parses a date in a specific format from the regular expression extract of the transit text field. Here is a breakdown of what is happening.

• Extract the first 11 characters from the Transit Text. This is consistent across the field.
• In case it matches multiple times, I only want the first instance of the match returned.
• Convert the text into a date
• This field gets called “In Transit Since”
  

Below is a screenshot of the first field and the output.

To Summarize

Calculated fields in Tableau are very valuable to me. They allow me to transform my data without a lot of necessary code, and they are pretty intuitive. They also allow me to provide better access to information to my customers so that they can see important and otherwise hidden information about the system and their materials.

If you have any questions about calculated fields or Tableau in general please leave me a comment below. I will do my best to respond with a helpful answer.