Skip navigation

Sem título new.png

How to use the Grand Total to calculate the difference between 2 periods

 

As most of you know, Row Grand Totals allows us to see the Average, Sum, Minimum and Maximum by row.

 

But, what happens when we want to see the difference between two periods?

For example, you want to compare the Sales of 2013 x 2012 (or any other year).

Your desire output is a table (or a bar chart) with 3 columns: 2012, 2013 and the difference:

output.png

It seemed so easy to do... so I started to try to build the red column with Tableau.

After a couple hours, I did not find any solution to achieve that output.

Even the famous table calculations (like "Difference") did not fit my requeriments.

 

So, I went to the Community Forums to looking for a solution.

What a found (I hope I did not miss any simpler solution) were many ways to achieve those values, but not the layout that I wanted.

By the way, I suggest you to see this thread (which received many posts with different solutions) How to hide blank columns created by table calculations.

 

Should I give up and use the workarounds or maybe change the layout to fit the solutions I found?

No! There is always a way (or almost always).

 

I decided to put away all the crazy solutions that I was trying to develop and spend sometime thinking what I was missing...

After a while....That's it! .. much simpler than I imagined.

 

The solution that I figured out is relative simple:

you will need two parameters (one for the first year and other for the second year)

Two calculated fields: one to filter the years and other to calculate the Sales

+  Mark "Show Row Grand Totals" (Analysis -> Totals)


Calculated filed # 1 - Filter (Use this field to filter the "true" values)

(year([Order Date]) = [Year 1] or year([Order Date]) = [Year 2]) and [Year 1] <> [Year 2]

 

Calculated Filed # 2 - How to calculate the Sales

if last()=first() then

//GRAND TOTAL

sum( if year([Order Date]) =  [Year 2] then ([Sales]) else ([Sales])*-1 end)
else sum([Sales])

end

 

http://public.tableausoftware.com/static/images/Gr/GrandTotalDifference/Sheet2/1.png

 

Visit my personal blog Data Visualization & Preparation tips.

Sheet 16 new - Cópia.jpg

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:

Table.jpg

 

And you desire this output (where "New LOD" is the sum of Sales by Category):

Output.jpg

 

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.