
LOD Expressions #1 - Over (partition by) x LOD
Posted by Americo Guazzelli in Innovation Viz on Mar 4, 2015 3:20:00 AMOLAP function
If you are familiar with SQL and had to solve complex analysis with a single query, you've probably used the OLAP function "OVER (PARTITION BY)".
Basically, this function allows you to perform an aggregation in a different level of the view.
For example, you have this table:
And you desire this output (where "New LOD" is the sum of Sales by Category):
You might use role playing table, sub-queries or any other way that allows you to create the desired output.
But, if you want something easier and simpler to understand?
In that case you may be looking for OLAP functions.
By adding "over (partition by <field 1>, <field 2>,...<field N>)" you can choose the fields used to aggregate.
See the query below:
select Category, Sub-Category, Sales, sum(sales) over (partition by Category)
from (
select Category, Sub-Category, sum(sales) as Sales
from table
group by Category, Sub-Category
) A
In this query you are telling the database that for the second aggregation "sum(Sales) over (partition by Category)", you want to sum the Sales by Category.
In other words, you are saying that, for that particullary aggregation, you want the database to peform this:
select sum(Sales), Category
from table
group by Category
Nice, eh?
What about Tableau?
Before Tableau 9, to achieve that result you would have to use joins, data blending, sets or maybe table calculations (Compare a filtered dimension to all values), but all these require much more work and they have their limitation.
In Tableau 9, the new capability Level of Detail Expression (LOD Expressions), allows you to perform calculations which can gives you similar results than "over (partition by...)" - and much more!
So, to achieve our desired output in Tableau 9 you can create a simple calculated field:
Option 1: { EXCLUDE [Sub-Category]: SUM([Sales]) }
Option 2: { FIXED [Category]: SUM([Sales]) }
You can also use quick filter in or not in context (see attached Tableau 9 workbook).
The intention of this post is not to explain how the calculations works.
For more details about LOD Expressions visit
Tableau - Introduction to Level of Detail Expressions
VizPainter - My Favorite Tableau 9.0 Feature
The Last Data Blender - Understanding Level of Detail Expressions – Part 1
Also, visit my personal blog Data Visualization & Preparation tips.
-
LOD.twbx 1.2 MB
Comments