6
Assignments
Medium
Skill Level
20 min
Duration
Introduction
Calculated Fields are a powerful way to use and analyze data in Tableau Desktop. You can compare it to a calculation you can do in Excel, but much more more extensive: you create a new column each time. In this section of the free training you will learn how to create and use Calculated Fields. We also learn how to use Calculated Fields to perform advanced analyses analysis.
In this lesson you will learn
- How to create Calculated Fields
- What different functions there are
- What kind of calculations you can make
Creating Calculated Fields
Calculated Fields are fields (i.e., columns in the table) that are created based on other fields in the data. By creating Calculated Fields, you can manipulate data. manipulate and create new dimensions or measures that are not readily available in the data.
To create a Calculated Field in Tableau Desktop, select the option Create Calculated Field from the menu near the arrow in the window 'Data Pane.' Next, you write a formula that performs the calculation. This formula contains so-called functions that you can use in your calculation. In the next section we will discuss these functions.
Using Functions
Tableau Desktop offers a wide range of functions that you can use to manipulate data and perform advanced analysis. In Tableau, they are very conveniently divided into several categories. In the field where you create the Calculated Fields you can expand a sidebar by pressing the arrow on the right side of the screen. At the top right of this screen is a button 'All'. By clicking on it you see the different categories. To its right you will then see for each function you select what the function does, and how to use it. Note that the syntax is quite narrow, so pay attention!
These different categories mean the following:
String calculations allow you to edit String (=text) fields. Among other things, you can change certain columns to upper or lower case. You can also remove spaces left in your data by mistake.
Here you will find all the functions to edit date fields. So you can do math with date fields. For example, if you want to know how many days a package was in transit. You then calculate the difference between the shipping date and the delivery date.
In some cases it is convenient to change the data type of your column. For example, if you want to convert a date to text.
Logical functions allow you to perform tests on your data. An example is an if/then formula. For example, you test whether the profit is positive. Once the profit is positive, you can fill the column with the text 'Positive' and the other fields with 'Negative'. This can be useful if you want to color visualizations based on positive or negative profit.
As we have seen, Tableau automatically turns continuous values into an aggregation, a grouping. In our Bar Chart, for example, we calculated the total profit per product because Tableau automatically summed the profit per item by product group. SUM() is Tableau's default aggregation, but of course we want to be able to calculate averages or maxima in addition to summation, for example. These are all aggregate-functions that you can find in this list.
A number of functions you can use to make calculations with the user of the dashboard. For example, you can test whether or not a particular user belongs to a team and therefore should or should not see certain data in the dashboard.
Although all the data you visualize is in a (source) table, you can also translate the visualizations you create in Tableau into a small table. Table Calculations let you perform calculations on that table. For example, for a given product category, you can determine what percent of total sales that category represents. Tableau then first calculates the Sales by Category, and then how that translates to percentages.
Tableau also supports geographic maps. The Spatial functions contain a number of spatial calculations that you can use. For example, you can create a buffer around a particular point.
In short, You can functions Use functions to filter, to sort, to calculate and to aggregate. This allows you to gain insight into the data and the trends and patterns hidden in it. To practice, we have some examples of Calculated Fields.
See if you can find the SPLIT function and how it can be used within Tableau Desktop. What category is it under?
See if you can find the DATEDIFF function, what category is it under? And what do you think the function does?
Profit ratio
In the Sample Superstore-source we use already contains a Calculated Field. You can recognize it by the small =-sign in front of the column. Although we will find this field in the original source data, we can use it in the dashboard. The field called 'Profit Ratio', or the profit ratio. We can also modify this calculated field edit and see how the calculation was made. For a profit percentage, we want to divide total profit by total sales.
- Right-click on the field 'Profit Ratio'
- Choose the option 'Edit'...
- The Calculated Field now opens. Here you see the calculation with the Functions used.
Functions are given a blue color. Columns/fields used in the calculation have an orange color. This way you can easily distinguish them. Furthermore, we see the function SUM. The SUM-function adds up all the values of the field named inside the parentheses. We use that function here first for the fields themselves, because we need the total gain by the total sales sales. If the only profit/sales as a formula, Tableau calculates for each row in the data set (i.e., for each individual sale) the profit ratio, and then adds all these margins together. As a result, we get an incorrect result. So it is important that we use SUM. So we divide the SUM of profit by the SUM of sales.
Tip: with two '//' characters, we can enter a comment in the calculated field. Useful if colleagues later want to view or modify the explanation of a calculation.
4. Type a comment by starting with //. The text turns gray, so you know that the comment has no further influence on the calculation.
Calculating with text fields
As mentioned earlier, we can also "do math" with texts. This may sound crazy, but we are going to explain it further in this exercise.
Suppose we want to know how often someone with the first name Mark placed an order. This will be a tricky job, because in our data we only have a name combined with a last name. Therefore, we are going to use a Calculated Field that contains only the first name. We will use the function Split.
- Open a new Calculated Field
- Rename the Calculated Field to 'First Name'
- Next, start typing the Function we want to use: SPLIT. Tableau automatically gives suggestions for functions that begin with the letters you type in.
- Choose the SPLIT-function. On the right side of your screen you can see how the SPLIT-function works. If you don't see anything, click on the word Split and possibly the right arrow to expand the function menu. We need to go to a textfield in the dataset, a letter or character choose a letter or character on which we want to split the text, and enter a number for which part of the split text we want to use.
- Start with the column we want to split, 'Customer Name'.
- Then type a comma.
- Since we want to split based on a space between the first and last name, we type two single quotes with a space. This way, Tableau knows that the field should be split by a space. ' '.
- Then type another comma.
- Then tap a 1. This 1 indicates that we want the 1st part of the split text.
Your calculation now looks like this:
Now create a new worksheet Create.
- Rename this sheet to 'Mark'.
- Now double-click your just created Calculated Field, First Name.
- Double-click on the column Customer Name.
- What do you see?
Don't forget to save your work 🙂 .
TIME Difference between date VALUES
Let's move on to a slightly more difficult example. We would like to know how many days it takes for a customer's order to be shipped is shipped. It would be a chore to manually calculate for each order the difference between the Order Date and the Shipping Date. Fortunately, with Tableau's Calculated Fields, we can easily create a calculation to answer this question for all orders at once.
- Open a new Calculated Field;
- Rename the field to 'Days until shipping';
- Start typing, and select the function DateDiff. In the menu, see what the syntax of this function looks like;
- Then type, between the function brackets, 'day' in quotation marks. This is because we want to have the difference in days;
- Follow with a comma;
- Start typing, and select 'Order date';
- Follow with a comma;
- Start typing, and select "Ship data.
Your Calculated Field looks like this:
On a new sheet you can add the following columns use for a visualization: Days until shipping on the columns shelf, and Order ID on the rows shelf. The resulting bar chart shows for each order how many days it took from order to shipment.
...At least, if we assume that each Order ID contains only one line.
Since Tableau automatically aggregates with SUM, we cannot tell if this is the sum of 1, 5 or 100 lines. So it is always important to keep an eye on what Tableau is doing and what possible consequences it might have. There are several ways to test the outcome of our calculation.
- Right-click on Days Until Shipping and choose Measure > Average. With that, Tableau divides the sum of the number of days per Order ID by the number of lines that make up that sum.
or - Make sure Days Until Shipping as sum is aggregated. Right-click on one of bars in your screen of a Order ID with a very long delivery time. Click on View Data.... A pop up window will now open. If you now click on the name of the source table here, you will see the how in the visualization present fields in the table. In this case, the order probably contains 6 products, so there are 6 lines.
or - Drag from the Data Pane again the field Order ID To the Columns Shelf in addition to Days Until Shipping. Right click on Order ID in the Columns Shelf and select Measure > Count. Now you see that the field Order ID turns green. At the top right of your screen, click on Show Me and select the table, at the far top left. We can now see that the Order IDs with high values for Days Until Shipping in many cases also occur more frequently in the dataset. Coincidence? You name it, you're the analyst!
or
Conclusion
In this section, you learned how to Calculated Fields and Functions can use in Tableau Desktop to manipulate data manipulate and advanced analyses perform advanced analysis. By using these functions, you can gain insight into the data and the trends and patterns hidden in it. It makes Tableau a powerful and flexible product. You can solve all your data queries using these calculated fields. In the next topic, we will continue with how to filter data in Tableau.