Table Calculations:
These Calculations are a special type of Calculations in the Tableau, we use these calculations to create calculations over calculations to get the more detailed level analysis. For example we calculate the Sum of sales by Year then we use Table Calculations to see Year over Year Growth or What is the Contribution of each Year when we compare with other years within the analysis.
Important Terminologies:
Addressing: The fields which are used to create Table Calculations is called Addressing. (The dimensions that define the part of the table you are applying the calculation to are called addressing fields.)
Partition: The fields which are used to split into pieces are called Partition.(The dimensions which are used to perform the groupings are called “Partition”.
The fields which are used for Grouping purposes.)
Pane: Once divide the entire view into pieces, then each piece is called a pane.
Down: Calculation will take place from top to bottom direction (Vertically).
Across: Calculation will take place from Left to Right direction (Horizontally).
Calculations: We use different kinds of calculations in Table Calculation, the following are lists of Calculations.
Running Total
Difference from
Percentage Difference
Rank
Percentage of Total
Moving Average
In the following example, Years on Columns, Products on Rows.
Row - Product - Across - Left to Right (Direction)
Column - Year- Down - Top to Down (Direction)
--------------------
In this Example, We are calculating Running Total using Sales, Region.
-------------------------------------
In this Example, We are calculating Percentiles using Sales, Region.
------------------------------------------------------------------------------
In this Example, We are calculating ranks using Sales, Region, High Sales is ranked as 1 then followed by next highest Sales is ranked as 2......
Here we are using two Dimensions to Apply the Table Calculations. We are going to use this report to see the results after applying Table Calculations.
-------------------------------------------------------
Running Total from left to right. Compute using Table(Across))
Addressing: Year
Partition: Region
This example shows the running total of each region separately by summing up each year .
------------------------------------------------
Running Total from Top to Bottom
Addressing: Region - Calculation move in this direction
Partition: Year - Calculations start from each Year
This example shows the running total of each year separately by summing up each region.
----------------------------------------------------------------------
Difference From Previous Value from Left to Right.
Addressing: Year - Calculation move in this direction
Partition: Region - Calculations start from each Year
--------------------------------------
Here we are applying Percentage of total(Down) it means calculation over the Region within each Year.
Each Year is a Partition. And calculations are moving from top to bottom. It means Addressing the Region.
—-----------------------------------
Here we are applying Percentage of total(Across) it means calculation over the Years within each Region.
Each Region is a Partition.
Calculations are moving left to right, it means Addressing on Years.
https://help.tableau.com/current/pro/desktop/en-us/calculations_tablecalculations.htm
Some Important Window Functions:
First()
this function gets the integer as the output it means number of cells away to reach the first cell. ( -int, indicates we have to move backward to teach the first cell)
Last()
this function gets the integer as the output it means number of cells away to reach the last cell. ( int, indicates we have to move forward to teach the last cell)
index()
This function generates the sequence numbers starting from 1.
In the following example, Year is partitioned into panes, so each pane starts applying first, last, index functions.
4)LOOKUP:
This function is used to
navigate to previous rows or columns
or next rows or columns.
6) Running_AggregateFunction:
We use this function to get the running aggregate values, it means from starting point to the current point and on top of that apply the aggregate function.
The following screenshot tells the running total of sales along with the years, partition by months.(Left to right direction)
—--------------------- —-----------
The following screenshot tells the running MAX Sales along with the years, partition by months.(Left to right direction)
—-------------------
The following screenshot tells the running MINIMUM of sales along with the years, partition by months.(Left to right direction)
—--------------------
The following screenshot tells the running MINIMUM of sales along with the Months, partition by Years.(Top to Bottom direction)
---------------- —------------------- —---------------
The following screenshot tells the running total of sales along with the Months, partition by Years.(Top to Bottom direction)
--------------------- —-----------
5) WINDOW_AGGREGATEFUNCTION:
This function works on the entire table and splits into partitions based on the fields we use or using the pane down or pane access option. It tells us what the aggregate is within the window.
In the following example, we are seeing all aggregate functions pane_down i.e. partition by years and addressing by Regions.
—----------------------
In the following example, we are seeing all aggregate functions pane_down i.e. partition by years and addressing by Regions. Total Sales by region within each year.
—--------------------- —-----------
In the following example, we are seeing all aggregate functions pane_down i.e. partition by years and addressing by Regions. MAX Sales by region within each year.
—--------------------- —-----------
In the following example, we are seeing all aggregate functions pane_down i.e. partition by years and addressing by Regions. MAX Sales by Year.
--------------------- —-----------
In the following example, we are seeing all aggregate functions pane_across i.e. partition by years and addressing by Regions. sum of each quarter across the Years partition by Quarter.
LOD: Level of Details
We LOD expressions to do the calculations at a very lower granular level or at a higher granular level it depends on the business needs.
Granularity - Level detail of data
Date: Year(Low Granularity = Exclude) -> Quarter -> Month -> Week -> Day(High Granularity = Include)
Geography: Country(Low Granularity= Exclude) -> Region -> State -> City(High Granularity= Include)
In Tableau a more granular level (INCLUDE), a less granular level (EXCLUDE), or an entirely independent level (FIXED) and it is similar to group by in SQL.
Fixed:
If we don't specify any dimension for Fixed LOD expressions then we get the calculation at table level.
—----
If we specify any dimension for Fixed LOD expressions then we get the calculation at specified level.
In this example, we are getting total sales at Region level.
—--- —--- —----
If we specify any dimension for Fixed LOD expressions then we get the calculation at specified level.
In this example, we are getting total sales at Region & Category level.
If we specify any dimension for Fixed LOD expressions then we get the calculation at specified level.
In this example, we are getting Max sales at Region & Category level.
If we specify any dimension for Fixed LOD expressions then we get the calculation at specified level.
In this example, we are getting Min sales at Region & Category level.
----------------------------------------------
If we specify any dimension for Fixed LOD expressions then we get the calculation at specified level.
In this example, we are getting the Number of Records at Region & Category level.
—-------------- —---------------- —------------------ —--------------------
Exclude:
If any dimension is part of the view but we don't want to include in the calculations then we use this function.
If we specify any dimension for Exclude LOD expressions then we get the calculation by ignoring the specified dimension. In this example, we are getting total sales at Region level, even though we have Category in the view since we are Exclude the dimension Category.
If we specify any dimension for Exclude LOD expressions then we get the calculation by ignoring the specified dimension. In this example, we are getting total sales at Region, Segment level, even though we have Category in the view since we are Exclude the dimension Category.
If we specify any dimension for Exclude LOD expressions then we get the calculation by ignoring the specified dimension. In this example, we are getting total sales at Region level, even though we have Category, Segment in the view since we are Exclude the dimension Category, Segment.
Include:
If any dimension is not part of the view but we want to include in the calculations the results in the view then we use this function.
If we specify any dimension for Include LOD expressions then we get the calculation by including the specified dimension. In this example, we are getting AVG sales at Region, Category, Customer level, even though we have only Region, Category dimensions in the view since we are using Include Function along with the dimension Customer Name.
—-------------- —---------------- —------------------ —--------------------
If we specify any dimension for Include LOD expressions then we get the calculation by including the specified dimension. In this example, we are getting MAX sales at Region, Category, City level, even though we have only Region, Category dimensions in the view since we are using Include Function along with the dimension City level.
No comments :
Post a Comment