Sign in or start a free trial to avail of this feature.
1. How Formulas are Built in Tableau
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 in Excel, it's possible to write many different formulas to manipulate your data In this course, we are 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 etcetera 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 calculate field is a field that you would like to generate in your dataset 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 it to your visualizations Formulas that exist in shelves are not normally written by the user but are pre-generated in Tableau from dropdown 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 dropdown and going to measure and in here, I have other aggregate functions that I can use such as the minimum, maximum, average, median count, etcetera.
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 3389 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 in the dropdown 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 dataset, we are 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 dataset 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 will load our data into Tableau and begin using some aggregate functions.