Grand Total Difference

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:

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