How to...make Calculations

Your data will hardly ever be perfect for what you want for your analysis. Calculations are one of the key instruments you have to make the data suitable for answering the questions you and your peers have of the data. Many people fear calculations but there are some simple bits of knowledge that will help unlock the power and flexibility that calculations can give.

What do calculations do in Data Preparation?

On the most simplistic level, calculations create a new data field, or overwrite an existing one. Most calculations use an existing data field as the base to form the new data field. The new or altered field is referred to as a calculated field, referencing the fact it did not originate in the underlying data inputted in to your preparation flow. New data fields are created due to:

• Being cleaned - remove unwanted characters, split names etc
• Arithmetic - sum, average or multiplication results
• Created from other multiple fields - form dates, email addresses

The new data fields then become available for analysis or further preparation. A new field is created in Prep unless the calculation is named identically to an existing data field, in which case the originally named data field is replaced. 

How to start creating a calculated field

There are two main ways to create calculated fields in Tableau Prep. Firstly, underneath the flow pane where either the Profile or Data pane are shown, there is often a quick link to creating a calculation.

This opens up a separate window where the calculation is formed but is blank as there has been nothing specified to be included.

The second place to create a calculated field is from a specific data field. By hovering over a field that you are likely to use in the calculation and selecting the menu designated by the '...' symbol, the same calculated field dialogue box is opened but this time with that data field added to the blank white space. 

This is due to Prep assuming that you have clicked on the 'Create Calculated Field...' from the menu on this data field for a reason. 

Key Fundamentals for Forming Calculations

Within the Calculation Dialogue box pop-out there are a couple of elements that can really help you learn and then master calculations. 

The first element is the dropdown reference list of calculations. If you are new to calculations in data tools, this is a massive support in finding the best function that will help you use calculations to get the answers you require. 

The functions related to each of the subjects listed will help narrow down what functions may help. Accompanying this is the second element of support and that is the syntax, description and example of each function that is provided in the reference pane. 

In this example, the Datepart function, used to pick out elements of a given date, gives all of the useful support to help understand how to form the calculation, what it does and an example of how to use it. 

Syntax

Even as an experienced Tableau, or data software user, you will frequently check the syntax of the function. The way to read the syntax is as follows:

                  FUNCTION(anything lower case is required, [anything in square brackets is optional)

When forming functions, it is important to remember to match all the aspects of the syntax that are not in square brackets as they are not optional. Although, when you form your calculation, your data fields will have square brackets wrapped around the name of the field, this isn't the same thing and can be confusing to new users. 

Description

The description is where most new users will spend time to understand what the clicked on function actually does. The names of the functions are often a good clue to what they are likely to be used for but the description gives us more detail on exactly what task that function is intended for. 

Example

The example can be helpful for all levels to remember what is meant by 'date_part' or how to encode the 'start_of_week'. Some of the examples are not the easiest to understand so don't forget that Googling to find more examples is a great way to support your understanding even further. Just put Tableau and the function name in to a search engine and you'll find more examples and often a blogpost or two to support even more. 

Building the calculation

With all this support from the Prep tool, building calculations should be easy?! Well not quite. Calculations can be really complex and it only takes catching another key on your keyboard to break the calculation you are writing. 

When calculations go well

Calculations go well when use a function, or functions, to form a calculation that Prep assesses to be correctly formatted. At the bottom left corner, Prep, like Tableau Desktop, informs the user that the calculation is valid. This does NOT mean that the calculation is correct and will return the data field you require. Getting the correct data is purely your own assessment as only you know what you were trying to do. 

Some of the key elements of this part of the screen is the colouring used in the calculated field:

• Blue things are functions that can be found and understood using the references above
• Orange things are the data fields in your data set. If they are black you are either missing the square brackets or have misspelled the data field's name. 
• Grey things are comments and a great way to share your logic for future reference for others as well as reminders for yourself. You can set comments by add '//' to the start of a line in the calculation editor

When calculations do NOT go well

When calculations contain syntax errors, everything goes red. This can be frustrating as not only is Prep showing an error, but also, only by removing that mistake, do the useful colours listed above return. This can mean that when writing long logical calculations like IF or CASE statements, it's better to add an 'END' to the calculation so the validity of the syntax gets retested frequently allowing the author to understand if they are formulating the calculation correctly.

Types of Calculations

So you know how to navigate all the aspects of creating a calculated field in Tableau but what are you likely to do with them? Here's a list of common types of calculations to help frame some of the use cases you may come across:

Numerical 

Sum, average and all other basic arithmetic is covered by Numerical functions. The main thing to note is whether you are using Integers or Float data as Prep will occasionally error if it is expecting the other type.

String

Cleaning and manipulating string data fields will be a fundamental part of your data preparation life. Whether it is splitting, changing case or removing unnecessary characters, string functions will become very familiar quickly.

Dates

The way Tableau handles dates is fantastic. Using it's own internal calendar to assess what weekday a certain day is through to being able to pick apart dates with ease is a joy to use compared to coding each element in SQL or other coding languages. Being able to add date intervals or working out the difference between dates, these functions will become very familiar.

Boolean

These calculations perform very well as they simply return True or False. Using certain functions from the other types of data calculations may return boolean results.

Logical

IF and CASE statements can take a lot of getting used to if you haven't used the functions in other tools before. Adding logical calculations becomes important when attempting to solve more complex problems. The time spent practising will pay you back.

Type Conversion
Tableau Prep expects certain elements of the calculations used to be a prescribed data type. Therefore, using functions like Int() or Str() to convert fields within a calculation to be the correct version can be a huge time and complexity saver. 

For some calculations are the bane of data preparation and for others, their fun logic puzzles. Which ever side you sit of that argument, calculations are something you will have to use and understand in order to be successful at preparing data.