Aggregation rules in LinPack

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 Use Cases
Aggregations applicable on measures
Sum of Returns the sum of all values in the expression.
(Null values are ignored)
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)
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 (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)
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')
Average of Returns the average of all values in the expression.
(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)
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 Returns the minimum of all values in the expression.
(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 Returns the maximum of all values in the expression.
(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 Returns the median of all values in the expression.
(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)
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)
Aggregations applicable on dimensions
Count Distinct of Returns the numbers of distinct items.
(Null values are not counted)
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)
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)
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 Returns the numbers of items.
(Null values are not counted)
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)
Aggregations applicable on 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)
Constant
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? :-)


You might also be interested by

LinPack for Tableau - DEMO

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

Read more 
By Nicolas OuryOn November 25, 2019

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:

Read more 
By Nicolas OuryOn January 18, 2019

Create your KPI Calculations in Tableau, automatically, for free!

Create your Tableau calculations for free with LinPack: do not spend time writing formulas, create amazing DataViz!

Read more 
By Nicolas OuryOn October 19, 2018

Creating a KPI with LinPack

Creating a KPI with LinPack

Read more 
By Nicolas OuryOn November 22, 2017

Create dashboards with LinPack: Custom Configuration vs Business Template

LinPack offers you two efficient methods to create Tableau dashboards: 'Custom Configuration' vs 'Business Template'. This news is about explaining the difference between the two creation modes and in which cases you should use one or the other.

Read more 
By Nicolas OuryOn November 15, 2017