Data Aggregation in Tableau
In Tableau, the aggregate function allows you to summarize your data, an aggregation performs a calculation on the set/group of values and returns the single value.
For example, you might want to know exactly how many orders your company had for a particular year. You can use the COUNTD
function to summarize the exact number of orders your company had, and then break the visualization down by year.
You can aggregate either Measures or Dimensions in Tableau, but most commonly we used to aggregate Measures.
Whenever you add a measure variable to the rows or columns, the aggregation has been applied to it by default. The type of aggregation applied varies depending on the context of the view.
Tableau Has some of the Predefined functions which are explained below with an example
Introduction to Tableau
 This function returns the sum of all values in the given expression. SUM can be used with numeric fields only. Null values are ignored.
 The syntax for the SUM() function is as below.
SUM(Expression)
The following example demonstrates the SUM() function:
 Repeat the steps from AVG() function from starting till opening the Create Calculated Field.
 Once the create calculation field has opened, enter the name as SUM. Sales and enter the formula to find the sum of sales as SUM([Sales]).
 The SUM(Sales) field has been created under the measure, drag SUM(Sales) to the row shelf, you will see the below graph.
 If you drag AGG(SUM.Sales)) to the color shelf and label shelf, you can clearly differentiate between the sales and SUM sales as below.
Data Sorting in Tableau
The following example demonstrates the MIN() function:
 Repeat the steps from AVG() Function from starting till opening the Create Calculated Field.
 Once the create calculation field has opened, enter the name as MIN. Sales and enter the formula to find the sum of sales as MIN([AVG.Sales], [SUM.Sales]).
 Now, the MIN. Sales field has been created under the Measure, Drag MIN. Sales to the row shelf, you will find the graph which shows the MIN(Sales) values out of AVG(Sales) and SUM(Sales) value.
 Dragging the AGG(MIN.Ssales) field to the Colors and Labels tab in the Marks shelf helps you to highlight the shades.
Formatting in Tableau
The following example demonstrates the MAX() function:
 Repeat the steps from AVG() Function from starting till opening the Create Calculated Field.
 Once the Calculated field has opened, name the Filed as MAX. Sales and enter the formula to find out the maximum sales value by comparing AVG(Sales) and SUM(Sales).
 Now, a new field named MAX. Sales have been created under measure, drag MAX. Sales field to row shelf.
 Next, drag MAX. Sales to the color tab and label tab in the Marks shelf, then you will find the graph as below, which shows the Maximum sales value by comparing AVG(Sales) and SUM(Sales).
Boxplot chart in Tableau
 This function returns the statistical variance of all values in the given expression on the entire given sample.
 The syntax for VAPR() function is as below:
VARP(Expression)
The following example demonstrates the VARP() function:
 In this example, we are going to find the complete variance of the Profit over the Order Date.
 Repeat the steps from VAR() Function and from the starting till open Create Calculated Field.
 Once the Create Calculated Field window has been opened, Name the field name as VARP. Profit and enter the formula as VARP([Profit]).
 Now, a new field has been created with the name VARP. Profit under the measure, drag this VARP.Profit to the row column.
 And drag VARP.Profit to the color tab and label tab in Marks shelf.
 The below graph will be displayed which shows the complete variance of the profit Order Date as shown below.
Line Chart in Tableau
 This function returns the statistical standard deviation of all values in the given expression based on a given sample.
 The syntax for STDEV() function is as below:
STDEV(Expression)
The following example demonstrates the STDEV() function:
 In this example, we are going to find the standard deviation of sales in the given data source over Order Date.
 Repeat the steps from AVG() Function from starting till opening the Create Calculated Field.
 Once the create calculation field has opened, enter the field name as STD. Sales and enter the formula to find the standard deviation of Sales as STDEV([Sales]).
 Now, the new field named STD. Sales have been created under measure, drag, STD. Sales to the row shelf.
 Drag STD.Sales to the color tab and label tab to the Marks shelf to highlight the values.
Tableau Dashboards
 This function returns the statistical standard deviation of all values in the given expression based on a biased population.
 The syntax for the STDEVP() function is as below:
STDEVP(Expression)
The following example demonstrates the STDEV() function:
 In this example, we are going to find the standard deviation of the whole sample from the Sales field.
 Repeat the steps from the STDEV() Function from starting till open Create Calculated Field.
 Name the calculation field as STDP. Sales, enter the formula as.
 Now, the new field called STDP. Sales filed has been created under the measure, drag STDP.Sales to the row shelf.
 Next, drag STDP.Sales to the color and label tab in the Marks shelf then the below graph will appear. Which shows the overall standard deviation of sales over the order date.
Waterfall Chart in Tableau
 This function returns the number of distinct items in a group. Null values are not counted.
 The syntax for COUNTD() function is as below.
COUNTD(Expression)
The following example demonstrates the COUNT() function:
 In this example, we are going to find the distinct number of Product IDs state wise.
 Open Tableau and click on Connect to Data.
 Then the Connect page will open, click on Microsoft Excel.
 Navigate to the downloaded files, select Sample Superstore excel file, which is a saved excel file, and click on Open.
 You can download a Sample Superstore.xls file from the link: https://chercher.tech/files/
 Once the data source has been connected, open a new worksheet by clicking on sheet1.
 Drag the dimension variable Product ID to the row shelf and another dimension variable State to the Column shelf.
 Click on Analysis and select the Create Calculated Field.
 Name the field as COUNTD.Product ID and enter the formula to find the unique number of product ID as COUNTD([PRodcuct ID])
 The new field COUNTD.Product ID has been created, drag this field to row shelf and to color tab and label tab in the Marks shelf.
 The below will be obtained which shows the count of distinct Product Id in State wise.
Below Shows the List of Rarely Used Tableau Functions:
Function 
Syntax 
Definition 
COLLECT 
COLLECT (spatial) 
 An aggregate calculation that combines the values in the argument field. Null values are ignored.

CORR 
CORR(expression 1, expression2) 
 Returns the Pearson correlation coefficient of two expressions.

COVAR 
COVAR(expression 1, expression2) 
 Returns the sample covariance of two expressions.

COVARP 
COVARP(expression 1, expression2) 
 Returns the population covariance of two expressions.

MEDIAN 
MEDIAN(expression) 
 Returns the median of an expression across all records. Median can only be used with numeric fields. Null values are ignored. This function is not available for workbooks created before Tableau Desktop 8.2 or that use legacy connections.

PERCENTILE 
PERCENTILE(expression, number) 
 Returns the percentile value from the given expression corresponding to the specified number. The number must be between 0 and 1 (inclusive)—for example, 0.66, and must be a numeric constant.

Tableau Story Line
You can use the function reference to find and add aggregate functions and other functions (like the logical IIF function in this example) to the calculation formula.
 And, name the calculation field as Calculation and then click on Ok.
 Once you click on Ok in the Calculation window, you will find the new field Calculation appears under Measures in the Data pane. Just like your other fields, you can use it in one or more visualizations.
 When Calculation is placed on a shelf or card in the worksheet, its name is changed to AGG(Calculation).
 And, the below graph will appear which shows the aggregation calculation using the measure variable Sum(Sales).
Tableau Forecasting
 For any aggregate calculation, you cannot combine an aggregated value and a disaggregated value.
For example, SUM(Price)*[Items]
is not a valid expression because SUM(Price) is aggregated and Items is not. However, SUM(Price*Items)
and SUM(Price)*SUM(Items)
are both valid.  Constant terms in an expression act as aggregated or disaggregated values as appropriate.
For example SUM(Price*7)
and SUM(Price)*7
are both valid expressions.  All of the functions can be evaluated on aggregated values.
 However, the arguments to any given function must either all be aggregated or all disaggregated.
For example, MAX(SUM(Sales), Profit)
is not a valid expression because Sales is aggregated and Profit is not. However, MAX(SUM(Sales), SUM(Profit))
is a valid expression.  The result of an aggregate calculation is always a measure.
Tableau Time Series Analysis
When you add any measure variable to the rows column, Tableau automatically aggregates its value. Average, Sum, and Median are the common functions used for measure variables.
Sales become SUM (Sales), and every measure has a default aggregation, which is set by Tableau when you connect to a data source. You can change or view the default aggregation for measures.
 You can aggregate a measure using Tableau only for relational data sources.
 Multidimensional data sources contain data sources that are already aggregated.
 In Tableau, the multidimensional data source is supported only in windows.
Set Default Aggregation for Measures:
You can set the default aggregations for any measure variables. A default aggregation is the preferred calculation for summarizing a discrete or continuous field. The default aggregation is used when you drag a measure to a view automatically.
 Open Tableau and click on Connect to Data.
 Then the Connect page will open, click on Microsoft Excel.
 Navigate to the downloaded files, select Sample Superstore excel file, which is a saved excel file, and click on Open.
 You can download a Sample Superstore.xls file from the link: https://chercher.tech/files/
 Once the data source has been connected, open a new worksheet by clicking on sheet1.
 Rightclick on any of the measure variables in the Data field and select Default Properties then select Aggregation and then select one of the aggregation options as below.
 You cannot set default aggregation for the published data source.
 The default aggregation is set only when the data source is initially published.
 Default set aggregation of measure value will look as below.
When you are ready to make a data source available to other users, you can publish it to Tableau Server or Tableau Online.
Disaggregation of measures in Tableau:
 You can disaggregate the measured value, which means, the Tableau will display a separate pane for every data value in every row of your data source.
 Open Tableau and click on Connect to Data.
 Then the Connect page will open, click on Microsoft Excel.
 Navigate to the downloaded files, select Sample Superstore excel file, which is a saved excel file, and click on Open.
 You can download a Sample Superstore.xls file from the link: https://chercher.tech/files/
 Once the data source has been connected, open a new worksheet by clicking on sheet1.
 Create a view by dragging the Category and Sales to the Column Shelf and Order date to the Row shelf.
 Next, click on Analysis and select Aggregate Measures
 After selecting Aggregate Measure, you will see the graph below, which shows you that, Tableau collects individual row values from your data source into a single value that is adjusted to the level of detail in the graph as below.
You can aggregate any dimension variable in the view as Maximum, Minimum, Count, and Count Distinct. When you aggregate a dimension, you have to create a new temporary measure column, so the dimension takes on the characteristics of a measure.
 Consider the below view.
 Now, rightclick on the Category, under measure you will find the options to aggregate the dimension variables as shown below.
 If you select Minimum the below graph will appear, which shows the Minimum sales of the category(Furniture) as below.
 If you select Maximum, the below graph will appear, which shows the Maximum sales of the Category(Technology as shown below.
 If you select Count, the below graph will appear which shows the count of category.
 If you select Count(Distinct), then the below graph will appear which shows the Distinct count of Category in the given year wise is 3.
0 results