_edited_edited.png)
Introduction
Tableau is a business intelligence tool which helps us to analyze data in the form of visual manner maybe a graph, a report, a map etc. One of the most powerful features of the tableau is calculation field. In this blog we will talk about how to work with calculations in tableau and types of calculation field and its benefits.
What is calculation field?
The calculation field is a way to extend your data source. In the perfect world we do not need any calculations because we have all the points in our data set, we need but we know that does not exist. Our data sources are not perfect, they are often incomplete and messy. Sometimes we just need to derive some data points from logic, business rules or maybe some additional data points from existing data. Calculations allow us to have one more column we wish we have in our data source. It’s a flexible and powerful tool which allows us to create new dimensions such as segments, or new measures such as ratios. A calculation field can have many different components like field, parameters, a piece of text or an operator and many more functions.
How do we find calculation field in tableau?
In your sidebar, there is visual cue that allows you to find it. It starts with an equal sign; it means that this field is made up of some logic or expression that helps derive its value.
How to create a calculation field?
We can create calculations fields from many different places in tableau.
Drop down arrow right underneath your data source window. It will open a calculated field where you can type in and there is a little arrow which you can expend and get a list of all the functions that are available in tableau. It gives you description and example too.
A second way to create a calculated field is simply right clicking on a field in your sidebar.
Another way to create, go to analysis and create calculations field.
Simply double click on an empty area in column, row, and mark field. This is called Ad-hoc calculations. One limitation of Ad-hoc calculations is this will work only in the current worksheet. If you want to save it for use in other worksheets, then you must drag this over to the sidebar.
Types of Calculations Field
Tableau has four types of calculated fields:
Basic Row -Level Calculations
Aggregate Level Calculations
Level of Detail (LOD) Expressions
Table Calculation
1. Basic Row -Level Calculations
A row level calculation means that a value is going to be derived for every row or for every line that you can find from data source. It allow you to transform values at the data source. These calculations are performed for every row of underlying data. For example, you might calculate the number of days between the start date and end date. The resulting value would be calculated and available for each row of data.
Few examples are:
Contains string function: If we want to simply check the existence of a keyword or a text that may be embedded in another text field. Then we will create a calculation field
Drop this field in a row.
String concatenation + other string functions: If we have first name and last name and we needed to have a full name, then we will create a calculated field.
[first name] + “ “ +[ last name]
Drop this field in row.
2. Aggregate Level Calculations
These calculations are performed at an aggregate level. The view level of detail is defined by fields used as dimensions in the view. It is really taking a group of records and generating one result out of it. You might add or change the dimension in the view, and the aggregate calculation will be re-calculated to give the result at the new view level of detail. Aggregate functions can be used both on dimension and measures.
Simple aggregates (default measure behavior): In Tableau when you are dragging over any of the measures you will get sum of the measure by default. The way tableau works is all the measures are going to be automatically group by any of the discrete dimensions.
COUNTD for unique values: If we wanted to know the number of unique customers then we use this function.
Let’s create a calculated field: CountD ([customer Id])
3. Level of Detail (LOD) Expressions
LODs are very powerful calculations in tableau. It simplified a lot of the complex calculation. Level of Details expressions allow us to compute values at the data source level and the visualization level and give us more control on the level of granularity.
Types of LOD: There are three types of level of detail expressions
FIXED: Fixed level of details is the first type of LOD. The fixed level of detailed expression only looks at the expression of granularity .it does not care about the dimensions which are already present in the view. Fixed LOD returns a singular value.
Syntax: {fixed [ dimension]: sum([sales])}
Remember, here dimension list is optional. Without dimension also we can write it.
Syntax: {fixed: sum([sales])}
EXCLUDE: The exclude LOD respects and recognizes all the dimensions that are in the view but removes the dimensions that are in the expression in its calculation. the result of an excluded LOD is not a scalar value it will return its result as an Attr. exclude LOD are affected by dimension filters.
Syntax: {Exclude [dimension]: sum([sales])}
INCLUDE: This LOD is very much like the excluded LOD. The result is an aggregate in the form of the adder. The result of an include LOD is also affected by dimension filters. The only difference between include and exclude is that the exclude LOD subtracts or takes away a dimension in its own expression therefore it gives you a rolled-up value. it gives you a more aggregated value. The include lod includes more dimensions so it puts more detail in so it's more granular and less aggregated.
Syntax: {include[dimension]: sum([sales])}
3. Table Calculation
A Table Calculations are calculations on aggregated measures that Tableau sees in the virtual table. Tableau Calculations are a subset of calculated fields that compute the local data in Tableau. Table calculations are different from other types of calculations. They can flexibly and easily allow us to derive values based on what we have in our view. These are very powerful calculations. Table calculations work on measures, and they eventually replace these measures and they become brand new measures.
We can use table calculations for different purposes, including:
Converting values to rankings
Changing values to display running totals
Transforming values to show the percentage of total
Factors that affect table calculations:
Dimensions in the view will have a direct effect on the table calculations because the dimensions that we place in our rows or columns or in canvas or in the marks card or in your pages, these will determine the level of detail of our visualization. But if the dimensions we put in our tooltip, they are not going to affect table calculations. This means that how Visualization is built, which dimensions are added, and what data is filtered out all play a critical role in ensuring that Table Calculations work as intended. Table Calculations are generally affected by:
Layout
Direction (Addressing)
Scope (Partitioning)
Filters
Tableau also provides a simple solution called Quick Table Calculation, which is used to create such calculations. The steps to be applied in Quick Table calculation are:
Select the measure on which the table calculation must be applied and drag it to column shelf
Right-click the measure and choose the option Quick Table Calculation
Tableau Table Calculation Functions
Table Calculations in Tableau functions allow you to compute values in a table. Here are some of the basic Table Calculations in Tableau functions:
TOTAL (): It returns the total in a table calculation partition for the given expression.
RANK (expression, ‘asc’ | ‘desc’): It returns the standard competition rank for the partition’s current row. Identical values are assigned the same rank.
INDEX (): It returns the index of the current row in the partition, with no value sorting. The first-row index is 1.
FIRST (): It will return the number of rows between the current row and the partition’s first row.
LAST (): It will return the number of rows from the current row to the partition’s last row.
CONTAINS (expression, expression to look for): If the given string contains the specified substring, it returns true.
Calculation fields are one of the most powerful features of Tableau. With the help of it we can create new fields from existing ones, which are very useful to unlock new insights. Calculation fields take our analysis to the next level. To discover new possibilities and experiment with data, calculation fields play a major role in Tableau.
References:
Comments