KNOWLEDGE BASE

Grand Total for Difference Quick Table Calculation


Published: 25 Jan 2016
Last Modified Date: 11 Apr 2017

Issue

By default a quick table calculation difference will show null for the grand total.  

Environment

  • Tableau Desktop 9.0

Resolution

The grand total for month over month difference can be thought of as one big long chained equation:

 

Jan - Feb + Feb - Mar + Mar - Apr… etc.
 

As you can see in the short example above, the middle values all cancel out.  Leaving us with Jan - Apr.   Or more formula oriented, the value of the minimum month - the value of the maximum month.  In order to cohort those specific values in the grand total calculation we need to use fixed LOD expressions for { MIN(Month) } and { MAX(Month) }.  The final calculation is then:

 

IF SIZE() > 1 THEN ZN(SUM([Sales])) - LOOKUP(ZN(SUM([Sales])), -1) ELSE ZN(SUM(IF [Month] = { MIN([Month]) } THEN [Sales] END)) - ZN(SUM(IF Month = {Max([Month])} THEN [Sales] END)) END

Where [Month] is:

 DATETRUNC('month', [Order Date])

Did this article resolve the issue?