OLAP 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.