_edited_edited.png)
In Tableau, calculations are the most effective and adaptable element. Calculations are a very important part of making our dataset work for us. Our data might not contain everything we need and that is when calculations come into play. Calculated fields help us in creating new data from already existing data in our data set. When creating a calculated field, we are creating a new column and saving in our dataset. From this new column created, we can generate further visualizations. The best part is that our original data stays unaffected.
Reasons for Creating Calculated fields:
We can create calculated fields for a lot of reasons. Here are a few of them:
Segmentation of data: Data segmentation means dividing and organizing data into defined groups which helps in easier sorting and visualizations. It provides more clearer and actionable information that facilitates data analysis.
Filtering of results: Calculated fields help in filtering out the columns that are not necessary and visualize only those that are important for improved analysis.
Conversion of Data types: They help out in converting the data type of a field, for instance, converting a string to a date.
Calculating ratios: They play an important role in calculating ratios across different variables which facilitates saving database processing and storage resources.
Testing a concept: We may sometimes require to prove or test a particular concept and for this purpose, calculated fields permit us to play with data and explore new options.
Data Aggregation: Calculated fields play a crucial part in gathering and organizing raw data in a manner that is simple to analyze and aids in better visualization.
Taking advantage of built-in functions: Calculated fields allow us to benefit from the built-in functions which can be quickly used on our data.
Spotting calculated fields in Tableau:
In the sidebar, we can find a visual cue that helps identify the calculated fields easily. It will start with an equal sign, which implies that this field is made up of some logic and expressions that have been used to derive its value.
Types of Calculations:
In this blog, sample superstore data has been used for explanation purposes.
Tableau has three main types of calculations:
1. Basic Calculations:
This is further broken down into Row Level Calculations and Aggregate Calculations.
Row-level Calculations mean that a value is going to be derived for every row in the data source. For example, a split function to split the first name and last name and; a ‘makedate’ function to merge the year, month and day; calculating revenue from sales and quantity.
Aggregate Calculations means generating one result from a group of records. For example, calculating minimum or maximum profit, total sales and number of records.
Logical calculations: These are the logical functions that are commonly used in Tableau:
Function | Syntax |
IF | IF <expression> THEN <then> ELSE <else> END |
ELSEIF | IF <expression> THEN <then> [ELSEIF <expression2> THEN <then2>] [ELSE <else>] END |
IF NULL | IFNULL(expression1, expression2) |
CASE | CASE <expression> WHEN <value1> THEN <return1> WHEN <value2> THEN <return2> ELSE <default return> END |
MAX | MAX(expression) |
MIN | MIN(expression) |
IN | <expression1> IN <expression2> |
2. Level of Detail (LOD) Expressions:
They are very powerful calculations in tableau which allow us to compute values at the data source level and the visualization level. These expressions give us control over the level of granularity or level of detail we want the computation to be done.
Source: https://data-flair.training/blogs/tableau-lod/
More granular level (INCLUDE) - compute values using the specified dimensions in addition to whatever dimensions are in view. For example, calculating the sum of sales state wise
Less granular level (EXCLUDE) - prevents the calculation from using one or more of the dimensions present in the view. For example, sum of sales excluding the region.
Independent level (FIXED) - computes a value using specified dimensions, without referencing to dimensions in the view. For example, calculating the first purchase date per customer
3. Table Calculations:
These apply transformations on values within a visualization. For example running sum, moving average, and percent of total.
Three Steps to Create Calculated Fields:
Step 1: Creating Calculated Field
There are many different places where the calculated fields can be created:
1. Drop down the arrow right underneath the data source window. Click on ‘Create Calculated Field’ and it opens the calculated field editor. This is where we name the created field and type in the expression. The little arrow, when clicked, expands and gives a list of all the built in functions in tableau. It also displays the syntax of how to write it along with description and examples.
2. Second way is by right clicking on a field (that we wish to use in our expression) in the side bar, click ‘Create’ and then click ‘Calculated Field’. By doing this, the selected field prepopulates in the calculated field editor.
3. The third way is clicking on ‘Analysis’, then click on ‘Create Calculated Field’.
4. The fourth way is by double-clicking on the empty area in the columns or rows or marks card. It becomes like a text box where we can start typing our ad hoc calculations. The limitation of these calculations is that we don’t get the function list, so we should know the formula which we are using. It has to be dragged to the sidebar if we want to use other worksheets besides the current one.
Step 2: Writing the formula
A calculated field can have many different components.
Calculated field name
Comments : It can optionally have comments which has the documentation that we can embed with our calculated field to remember the logic behind the calculation for better understanding.
Field names that can be dragged into the formula.
String Literals: to embed a piece of text into the calculations.
Operators
Parameters
Functions: Functions are reusable codes that are built in tableau.
S ource: https://datacrunchcorp.com/tableau-calculated-field/
Step 3:
When finished, click OK
Conclusion
This blog described an overview about calculated fields that can be used to add new fields or experimenting with new data. After learning to create calculated fields, we can become a better analyst and create robust data visualizations.