 ## Aggregating measures in Tableau

Tableau comes with pre-defined aggregation functions such as:

• Sum
• Min
• Max
• Avg
• Count
• Count Distinct
• Variance
• Percentile
• Std Deviation

Those functions allows you to compute figures from raw to aggregated levels.

If those cover 90% of the cases, there are still 10% remaining (and complex) cases which are not covered by those functions.

## Some business cases are still not covered

For instance, you have a file containing the number of employees per month and per department: You would like to compute the total headcount.

By default, there is no aggregation rule in Tableau to do so easily. The SUM function would not work since SUM would aggregate headcount across months, which is obviously wrong.

What you want here, is an aggregation which sum headcount across Department, and this, only for the last (known) month. If you are a Tableau advanced user, you can create a formula for this: SUM(IF [Month]={INCLUDE: MAX([Month])} THEN [Number of Employees] END).

But if you are not a Tableau expert, or if you want to spend your time on more added-value tasks, you can leverage LinPack pre-built aggregation rules:

Pick-up the function you need and LinPack will create the correct Tableau formulas in your workbook!

## LinPack enriches Tableau with 30+ pre-defined aggregation rules

Aggregation Rule Description Sample Use Cases
Aggregate a num (sum, avg...)
Commonly Used
Sum of Returns the sum of all values in the expression.
(Null values are ignored)
Average of Returns the average of all values in the expression.
(Null values are ignored)
Minimum of Returns the minimum of all values in the expression.
(Null values are ignored)
Maximum of Returns the maximum of all values in the expression.
(Null values are ignored)
Median of Returns the median of all values in the expression.
(Null values are ignored)
Starting & Ending Position
Sum of (last period only) Returns the sum of all values in the expression only for the last known date.
For instance the value for Q1, will be the last know value in Q1, a sum will be applied on all other dimensions.
This aggregation rule applies for instance when counting stock volumes, headcount, measuring Portfolio position or Balance sheet accounts.
(Null values are ignored)
Sum of (first period only) Returns the sum of all values in the expression only for the first known date.
For instance the value for Q1, will be the first know value in Q1, a sum will be applied on all other dimensions.
This aggregation rule applies for instance when get the initial investment position in a Portfolio.
(Null values are ignored)
Average of (last period only) Returns the average of all values in the expression only for the last known date.
For instance, you have a data source containing a monthly snapshot of all your staff. You want to know the average age of your staff. You need to average the [Age] attribute only for the last know date. This is what 'Last Average' does.
(Null values are ignored)
Median of (last period only) Returns the median of all values in the expression only for the last known date.
For instance, you have a data source containing a monthly snapshot of all your staff. You want to know the median age of your staff. You need to median the [Age] attribute only for the last know date. This is what 'Last Median' does.
(Null values are ignored)
During a period
Sum of (from to) Returns the sum of all values in the expression for all records where the analyzed period is between From-Date and To-Date.
(Null values are ignored)
Average of (from to) Returns the average of all values in the expression for all records where the analyzed period is between From-Date and To-Date.
(Null values are ignored)
Minimum of (from to) Returns the minimum of all values in the expression for all records where the analyzed period is between From-Date and To-Date.
(Null values are ignored)
Maximum of (from to) Returns the maximum of all values in the expression for all records where the analyzed period is between From-Date and To-Date.
(Null values are ignored)
Median of (from to) Returns the median of all values in the expression for all records where the analyzed period is between From-Date and To-Date.
(Null values are ignored)
Other
Sum of (annualized) Returns the value of the given expression extrapolated for an entire year.
If you select one month, you will get the value of the month * 12.
If you select 2 months, you will get the sum of the two months, multiplied by 6....
Sum of (monthly average) Returns the sum of all values of the expression, divided by the number of months considered in the scope.
Sum of (for New) Returns the sum of the measure values for all new occurrences of the dimension (eg: Sum of 'Sales Amount' for New 'Customers')
Count something
Commonly Used
Count Distinct of Returns the numbers of distinct items.
(Null values are not counted)
Count of Returns the numbers of items.
(Null values are not counted)
Starting & Ending Position
Count Distinct of (last period only) Returns the count distinct of all values in the expression only for the last known date.
For instance the value for Q1, will be the last know value in Q1, a count distinct will be applied on all other dimensions.
This aggregation rule applies for instance when counting stock volumes or headcount.
(Null values are ignored)
New & Lost items
Count Distinct of New Returns the numbers of NEW distinct items. It actually counts the number of items having its first occurrence (it allows you to easily measure 'New Customers', 'New Products', 'New Employees'...).
(Null values are not counted)
Count Distinct of New (soft) Returns the numbers of NEW distinct items. It actually counts the number of items having its first occurrence (no new items will be considered on the first date) (it allows you to easily measure 'New Customers', 'New Products', 'New Employees'...).
(Null values are not counted)
Count Distinct of Ending Returns the numbers of ENDING distinct items. It actually counts the number of items having its last occurrence (it allows you to easily measure 'Ending Customers', 'Leaving Employees'...).
(Null values are not counted)
Count Distinct of Ending (soft) Returns the numbers of ENDING distinct items (no ending items will be considered on the last date). It actually counts the number of items having its last occurrence (it allows you to easily measure 'Ending Customers', 'Leaving Employees'...).
(Null values are not counted)
During a period
Count Distinct of (from to) Returns the count distinct of all values in the expression for all records where the analyzed period is between From-Date and To-Date.
(Null values are ignored)
Count of (from to) Returns the count of all values in the expression for all records where the analyzed period is between From-Date and To-Date.
(Null values are ignored)
Avg Lifetime of Returns the average number of years between the first and the last occurrence of the expression.
For instance, it allows you to easily compute 'Customer Lifetime', 'Product Lifetime'...
(Null values are ignored)
Get the time between two dates
Time between two dates
Avg Years between Returns the average number of years between two dates (the result is a numeric - float)
Avg Months between Returns the average number of months between two dates (the result is a numeric - float)
Avg Days between Returns the average number of days between two dates (the result is a numeric - float)
Avg Working Days between Returns the average number of working days between two dates (the result is a numeric - float) - Excluding Saturday and Sunday
Avg Hours between Returns the average number of hours between two dates (the result is a numeric - float)
Avg Minutes between Returns the average number of minutes between two dates (the result is a numeric - float)
Avg Seconds between Returns the average number of seconds between two dates (the result is a numeric - float)
Set a Constant Value
Commonly used
Constant Value Returns a constant numeric value.

## Examples

Let's consider this data set:

 Customer Jan-2018 Feb-2018 Mar-2018 A 10 10 10 B 14 25 10

Here are the calculations and results by Aggregation rule:

Aggregation Rule Grand Total - Formula Grand Total - Result
Sum of (10 + 10 + 10) + (15 + 25 + 10) 80
Sum of (last period only) Sum( last_know_value(10;10;10) , last_know_value(15; 25; 10)) = Sum(10; 10) 20
Average of ((10 + 10 + 10) + (15 + 25 + 10)) / 6 13.33
Average of (last period only) Avg( last_know_value(10;10;10) , last_know_value(15; 25; 10)) = Avg(10; 10) 10
Minimum of Min(10; 10; 10; 15; 25; 10) 10
Maximum of Max(10; 10; 10; 15; 25; 10) 25
Unique Value Unique Value (10; 10; 10; 15; 25; 10) Null
Unique Value of (last period only) Unique Value( last_know_value(10;10;10) , last_know_value(15; 25; 10)) = Unique Value (10; 10) 10
Count Distinct of Count Distinct(10; 10; 10; 15; 25; 10) 3
Count Distinct of (last period only) Count Distinct( last_know_value(10;10;10) , last_know_value(15; 25; 10)) = Count Distinct (10; 10) 2
Count of Count(10; 10; 10; 15; 25; 10) 6
Constant Value <NA> <NA>

Want to try it? :-)

## LinPack-for-Tableau - DEMO

Discover the LinPack experience: Create stunning Tableau dashboards automatically

10 minutes demo of (happiness with) LinPack-for-Tableau :-)

By Nicolas OuryOn January 8, 2021

## TUTORIAL - Creating a custom dashboard

Discover how you can create your own custom dashboard template online.

• Select your favorite data visualizations from our galleries.
By Nicolas OuryOn January 8, 2021

## TUTORIAL - Customizing a template

Discover how you can customize our dashboard templates to fit your specific needs.

• Rename or define template KPIs
By Nicolas OuryOn January 8, 2021

## TUTORIAL - Re-processing an existing LinPack dashboard

Discover how you can re-process an existing LinPack dashboard.
You will be able to:

• Redefine existing KPIs
• Add new DataViz from our galleries

.... all this preserving the customizations you would have done to your dashboard!

By Nicolas OuryOn January 8, 2021

## Tableau Best-practices: How to prepare your Tableau workbook to be efficient creating your dashboard

Efficient people in dashboarding takes time to carefully prepare their data source and have a great clean workbook before starting building Data Visualization.

Here-after, a must-do list to apply if you want to be efficient creating your dashboard: