Hi everyone,
I can't take credit for this as I received the answer 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/2015 | 2/1/2015 | 3/1/2015 | 4/1/2015 | 5/1/2015 | 6/1/2015 | 7/1/2015 | 8/1/2015 | 9/1/2015 | 10/1/2015 | 11/1/2015 | 12/1/2015 | Total |
---|---|---|---|---|---|---|---|---|---|---|---|---|---|
ACME Company (Net Revenue) | 2,055,677 | 2,713,388 | 2,795,421 | 1,588,655 | 2,153,322 | 1,327,778 | 2,944,860 | 1,507,287 | 2,290,217 | 1,746,109 | 1,500,000 | 1,023,000 | |
ACME Company (Net Revenue MoM Change) | 657,711 | 82,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
Comments