Sign in or start a free trial to avail of this feature.
1. Introducing the Case
Formulas in Tableau are often written in a shelf above the visualization. In this lesson, We'll go through how to do this and introduce the data set for this course.
To explore more Kubicle data literacy subjects, please refer to our full library.
Writing formulas in Tableau
- Tableau has its own Calculation Language that you will need for writing formulas
- Thankfully, this language is similar to Microsoft Excel and very easy to learn
- Formulas are typically written either in a shelf or in a calculated field
- Tableau also has some pre-built formulas, stored as Quick Table Calculations that are very popular
Introducing the case
- The dataset contains pharmaceutical sales data for 19 products
- Each row represents monthly sales of a product to a single customer
- The dataset comprises over 50,000 rows so download files will be larger than usual
In Tableau as an Excel, it's possible to write many different formulas to manipulate your data. In this course we're going to learn all the tools needed to build complex formulas in Tableau. Tableau has its own formula language that differs from Excel and SQL but thankfully it's not very difficult to understand and master. In fact, many of the functions that we use in Tableau will be familiar to you if you have used Excel before such as IF, SUM, MAX, Median, et cetera. In many ways, Tableau's formula language is superior to that of Excel because it allows you to create variables and also use more powerful functions such as CASE which we will see in a later lesson. In Tableau, formulas are normally written either in a shelf or in a calculated field. A calculated field is a field that you would like to generate in your data set but is not currently there. For example, you might want to divide profit by sales to calculate profit margin and have this value as a calculated field to add to your visualizations.
Formulas that exist in shelves are not normally written by the user but are pre-generated in Tableau from drop-down menus.
Let's move to Tableau quickly and see this in action.
When I drag a measure into a shelf, Tableau automatically creates a formula by taking the SUM function and performing this action on the field which is called value. And what this does is calculate the total value in the sheet.
I can adjust this formula by hitting the drop-down and going to measure and in here, I have other aggregate functions that I can use such as the Minimum, Maximum, Average, Median, Count, et cetera. Let's take maximum as an example.
So when I hit Maximum, this now tells me the maximum value for a single entry in my dataset of 3,389.
So as you can see when I add a dimension say fee earner to rows, the calculation is then applied to every value within this dimension.
Not only can I switch the function on the drop-down, I can also perform a quick table calculation which instead of calculating the Max will calculate the percentage of total. So when I select this option and go back to Sum this will show me the percentage of total revenue that each fee earner generates.
So while we almost always use formulas in Tableau, we don't necessarily write them from scratch. Now let's take a look at the case for this particular course. In our data set, we're going to examine pharmaceutical sales for 19 competing fictional products. The data contains monthly sales figures over a multi-year period and these figures are available on all customers for the 19 products in a specific region in the UK.
Note that for this particular case the data set is larger with greater than 50,000 rows so that the sizes of the before and after files underneath the videos will be bigger. In the next lesson, we'll load our data into Tableau and begin using some aggregate functions.