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, imagine 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 allowing 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).

This one will return the expected results.

 

LinPack-for-Tableau enriches Tableau with additional high-value aggregation rules

Instead of copy/pasting the preceding formula, LinPack simplifies this process by enriching Tableau with additional aggregation rules.

While configuring your dashboard in LinPack-for-Tableau, just pick-up ''Last Sum Of" as the aggregation rule and you will get a perfectly computed aggregation for Headcount:

 

When you create a Tableau dashboard in LinPack using 'Custom Configuration', you define your own KPIs using aggregation rules.

Here is the list of aggregation rules which are proposed by default in LinPack:

Aggregation Rule LinPack-for-Tableau
Exclusive Function
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 (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)
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 (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.

SUM OF (last period only)

Objective

You want aggregate your measure like this:

  • Keep only the last know value (across time dimension)
  • Sum across all other dimensions

Business Cases

  • Human Resources: Headcount, FTE...
  • Finance: BalanceSheet KPIs, Assets, Liabilities...
  • Banking: AuM, CAL...
  • Supply Chain: Inventory...
  • Heathcare: Number of patients...
  • ...

Do it in Tableau...

  • SUM(IF [Date]={INCLUDE: MAX(Date)}  THEN [your_measure] END)

... or use LinPack-for-Tableau to do it for you :-)

  • "Last Sum of"  [Headcount]

 

AVERAGE OF (last period only)

Objective

You want aggregate your measure like this:

  • Keep only the last know value (across time dimension)
  • Average values across all other dimensions

Business Cases

  • Human Resources: Headcount Age, Seniority...
  • ...

Do it in Tableau...

  • AVG(IF [Date]={INCLUDE: MAX(Date)}  THEN [your_measure] END)

... or use LinPack-for-Tableau to do it for you :-)

  • "Last Average of"  [Employee Age]


COUNT DISTINCT OF (last period only)

Objective

You want aggregate your measure like this:

  • Keep only the last know value (across time dimension)
  • Count the distinct values

Business Cases

  • Human Resources: Headcount...
  • Heathcare: Number of patients...
  • ...

Do it in Tableau...

  • COUNTD(IF [Date]={INCLUDE: MAX(Date)}  THEN [your_measure] END)

... or use LinPack-for-Tableau to do it for you :-)

  • "Last Count Distinct of"  [Employee Identifer]

 

UNIQUE VALUE OF (last period only)

Objective

You want aggregate your measure like this:

  • Keep only the last know value (across time dimension)
  • Return the value of the given expression if it has a single value for all rows, otherwise it displays an asterix (*)

Business Cases

  • Banking: Risk Indices...
  • ...

Do it in Tableau...

  • ATTR(IF [Date]={INCLUDE: MAX(Date)}  THEN [your_measure] END)

... or use LinPack-for-Tableau to do it for you :-)

  • "Last Unique Value of" [Risk Index]

 

COUNT DISTINCT OF NEW

Objective

You want to compute the number of new items which appear in your data set.

Business Cases

  • Sales: New Customers, Newly launched Products, New sales regions...
  • Human Resources: New employees, New positions, New departments...
  • Banking: New Accounts, New Products...
  • Supply Chain: New products in inventory...
  • ...

Do it in Tableau...

  • COUNTD(IF [Date]={FIXED [your_dimension]: MIN(Date)}  THEN [your_dimension] END)

... or use LinPack-for-Tableau to do it for you :-)

  • "Count Distinct of New" [Customer]

 

AVG LIFETIME OF

Objective

You want to compute the average duration of activity of your customers, products... (i.e average number of years between first and last transaction)

Business Cases

  • Sales: Customer Lifetime, Product Lifetime
  • Human Resources: Employee Seniority...
  • Supply Chain: Product Lifetime...
  • ...

Do it in Tableau...

  • AVG((IF [Date] = {INCLUDE :max([Date])} THEN DATEDIFF("day", {FIXED [your_dimension]:MIN([Date])}, MIN([Date],{FIXED [your_dimension]:MAX([Date])})) / 365 END))

... or use LinPack-for-Tableau to do it for you :-)

  • "Average Lifetime of" [Customer]

 

 

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? :-)