5 Replies Latest reply on Mar 1, 2016 4:08 PM by Patrick A Van Der Hyde

    Grand Totaling a Month-Over-Month Change Table Calculation

    joshua.rauh

      Hi everyone,

       

      I'll admit, this isn't a question so much as it is a sharing of an answer I received from the Tableau Developers tech support (thanks Asher) for an issue I think might be common. Here was the issue:

       

      I had connection to a Tableau dedicated data-source on my company's server that had Net Revenue information spread across monthly intervals. The goal was to calculate the month-over-month Net Revenue change and then total that out in a grand total column. The issue was, because month-over-month change was not a hard-coded value in the data-set, rather a table calculation, this prevented a grand total column from easily being added. I've pasted the test data I came up with below. The month-over-month change column was the table column using the formula ZN(SUM([Monthly Revenue])) - LOOKUP(ZN(SUM([Monthly Revenue])), -1):

       

       

      Metric
      1/1/20152/1/20153/1/20154/1/20155/1/20156/1/20157/1/2015
      8/1/20159/1/201510/1/201511/1/201512/1/2015Total
      ACME Company (Net Revenue)2,055,6772,713,3882,795,4211,588,6552,153,3221,327,7782,944,8601,507,2872,290,2171,746,1091,500,0001,023,000
      ACME Company (Net Revenue MoM Change)657,71182,033(1,206,766)564,667(825,544)1,617,082(1,437,573)782,930(544,108)(246,109)(477,000)(1,032,677)

       

      Rather than go into the details about how Asher did it, I'll just post his response & the workbook he sent me back below. ****NOTE: B/c I'm not looking for an absolute change, I had to slightly augment the formula he used to multiply the end result by -1. You can test it out in both Excel and Tableau to see what I mean. I'll bold the one change I made to the received copy below.

       

      ______________ From Tableau:

       

       

      Normally calculating a grand total of a table calculation is difficult.  It almost always requires coming up with a non-table calculation for the total to do so.  Luckily, this instance there is a non-table calculation possible using LOD expressions.

       

      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 [MonthOverMonthChange] ELSE ((  ZN(SUM(IF [Month] = { MIN([Month]) } THEN [Monthly Revenue] END)) - ZN(SUM(IF Month = {Max([Month])} THEN [Monthly Revenue] END))  ) *-1) END

       

      ______________

       

       

      Please see the attached workbook for the original solution I got, noting that it does not contain the change I made above.

       

       

      Enjoy.

       

       

      Joshua