A table calculation would solve your problem.
In your calculated field, you can use the PREVIOUS_VALUE() table calculation to access the previous month and calculate the difference to the current month.
Here is the official Tableau help on the topic: Table Calculation Functions
I also created a series together with SuperDataScience on the topic of Table Calcualtions. I am sure you will find it helpful: TABLEAU TABLE CALCULATIONS TUTORIAL - YouTube
1 of 1 people found this helpful
Welcome to Tableau (and the Community!)
Really like/admire your approach of not trying to do what you did in Excel in Tableau.
In the attached I've shown how you can do the Excel conditional formatting (this uses one of the Quick Table Calcs on the colour shelf)...to take others with you, it can be useful to incrementally change (and improve) what they are used to!
I've also done a completely different visual version, let me know if you like it and I can let you know how I did it. When you are trying to look at multiple clients with changes over month, that's a lot of information to communicate, so this kind of chart can be good so the user can focus on a single client, but see the rest (faded) in the back ground. Also when each client (or thing) are completely different in scale, referencing them from a single point can also be effective. Even more powerful if you put multiple charts in a dashboard so the "highlighting" can be done by clicking on a chart (say a bar chart showing scale and last month change, by Client)
Hope that helps.
Thank you Simon. I was trying to show the conditional formatting for just the current month column, but it is really hard to do so the best approach is to apply it to all the cells as you have done. I am looking through the calculation now to see how you done it and try to understand. I am trying to get a grasp of this myself - but thanks very much for your help. Also, thanks for the extra visualisation - very useful!
Glad it helped. Tableau in many ways is similar to Excel, and in other (significant ways) it's very different (certainly in the way it works, under the hood). The main difference, IMHO, is that Excel is cell based (you can do A1+C5+J36-[!sheet 4] K23), whereas Tableau operated over the whole of a column (this btw is why Tableau can handle millions, even billions, of rows of data but Excel struggles with a few hundred thousand).
There are however some "self-referencing" formulas...this is some of the uses for Table Calculations. I used the "quick table calculation" option to create this MoM one (where Tableau has built in this short cut for the most common uses). If we look at the formula
(ZN(AVG([Data])) - LOOKUP(ZN(AVG([Data])), -1)) / ABS(LOOKUP(ZN(AVG([Data])), -1))
the LOOKUP is the Table Calc part and the -1 means go back 1 level in the partition. You can define the partition (in this case we are using Table Across as the "compute using")
And as we have month in Columns, this means
(SUM of Sales for the Month - Sum of Sales for 1 month back)/ Sum of Sales for 1 month back
with the ABS and ZN to handle negatives and null values
equally (using the Edit Table Calc) I could have set it up specifically, like this
meaning do the -1 (LOOKUP) on Year, Month, Month Index (I'll get to this in a bit) and restart every Client name.
Hope that helps explain a bit of what's going on.
With regards showing only the last month, we can do this. There are many ways, and here is one
I created this calculation to index the months from zero (current) back from there using this calculation (this is a FIXED LoD and I won't muddy the waters by explaining this here!)
I can use this on the filter shelf to only bring back month index 0 and -1 (the last 2 months, and as it's dynamic 0 and -1 will always be the last 2 months)
Now we can't filter out Month Index = -1 as the LOOKUP would no longer have that month in the data in order to compute the month on month, but we can "hide" the -1
Now as we've hidden, not filtered the Table Calc will still work...and as we've hidden -1 and not March 2018 it will be dynamic, so we don't need to maintain it each month.
In the attached I've not hidden it, so you can do that and see how it works.