4 Replies Latest reply on Sep 14, 2018 6:06 AM by Simon Runc

    Visualise Difference Between Two Months

    Graeme Elliott

      Hi all,

       

      I have included a Tableau book which has some dummy client data for the past 12 months. What I would like to visualise is whether there has been an increase or decrease between the current month and previous month. What I would normally do in Excel is add some conditional formatting to compare the two columns and colour the current month's column red/green depending on whether it had increased/decreased when compared to the previous month.

       

      Now that I am using Tableau I am looking for a Tableau friendly way to visualise this, and I was hoping someone could help me by giving me some advice based on what they have done previously. The reason it is current month vs previous month is because the people who will look at these numbers want to see straight away where there are dependencies  rather than comparing quarterly or yearly data. However, if I am able to amend the calculate field (if one is used) to compare current month vs current month last year then that would be great too just for flexibility. What I am trying to do is not replicate what I do in Excel into Tableau - I want to see what other types of visualisation I can do.

       

      Any tips would be great! Thank yout!

        • 1. Re: Visualise Difference Between Two Months
          Jean-Pierre Labuschagne

          Hi Graeme,

           

          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

          • 2. Re: Visualise Difference Between Two Months
            Simon Runc

            hi Graeme,

             

            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.

            1 of 1 people found this helpful
            • 3. Re: Visualise Difference Between Two Months
              Graeme Elliott

              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!

              • 4. Re: Visualise Difference Between Two Months
                Simon Runc

                hi Graeme,

                 

                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!)

                 

                DATEDIFF('month',DATETRUNC('month',[Date]), {MAX(DATETRUNC('month',[Date]))})

                *-1

                 

                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.