7 Replies Latest reply on Jun 16, 2016 10:33 AM by Simon Runc

    Sorting a table based on most recent month over month difference

    Mathijs Versteeg

      Goal: Sorting a table by the most recent (aggregated) difference.

       

      Example

      On the right, I'd like to sort the customers (or sub-categories) by the most recent difference in Sales (december vs. november) in order to quickly see why sales were higher or lower in december. The color in these tables already indicate the difference. However, I can't seem to get the table calculation to

      1. be limited to only the most recent period, and
      2. make it appear in the sort dialog.

       

      I could only imagine it working with an LoD calculation combined with a Table calculation, which AFAIK isn't possible(?). What other options do I have of accomplishing this sort?

       

      https://public.tableau.com/static/images/MV/MV-Superstore/SalesDashboard/1.png

       

      Any help is greatly appreciated.

        • 1. Re: Sorting a table based on most recent month over month difference
          Simon Runc

          hi Mathijs,

           

          So to achieve this we need to employ a handy sorting trick...

           

          So first we need a formula, which contains the var on the last month, for every row (Customer ID) regardless of the month (i.e. even if we hover over a march cell, this still contains the value of the last months var, to previous month)

           

          We can do this with

          [Month over month diff sales - Last Month SORTER]

          ZN(LOOKUP(SUM([Sales]),Last())) - LOOKUP(ZN(SUM([Sales])), Last()-1)

           

          However, we won't be able to use this as our sort (as it's a table calc)...but Tableau sorts blue (discrete) pills in ascending order by default....so we can make this discrete and then bring it in before [customer],to force the sort...so as this, by default, sorts in ascending order, and we want descending order...we can just reverse the sign on our formula

          [Month over month diff sales - Last Month SORTER]

          ZN(LOOKUP(SUM([Sales]),Last()-1)) - LOOKUP(ZN(SUM([Sales])), Last())

           

          Then we can use this to force the sort. In the attached I've left this field un-hidden so you can see what it does, but in the final version you can hide this header!

           

          Let me know if that doesn't make sense, or doesn't do what you need.

          1 of 1 people found this helpful
          • 2. Re: Sorting a table based on most recent month over month difference
            Michael Hesser

            Great explanation Simon Runc... it took me the longest time to understand why & when to use Discrete vs.Continuous (and it's still tough sometimes), Your elegant explanation is wonderful!

             

            I'd also like to point out that use of ZN: if you haven't already, spend some time making friends with ZN-- what he lacks in characters (just 2!) he makes up for in calculation handiness!

            • 3. Re: Sorting a table based on most recent month over month difference
              Simon Runc

              Thanks Micheal, glad you found it useful...I do a bit of Tableau training here and there, and spend (for a starter group) 1/3 of the first day (2 day course) on just the concept of blue and green pills. Once you understand them you can generally create almost anything in Tableau, with enough imagination of how to use these elementary principles.

               

              The below link, although written back in 2011 (so a fairly primitive Tableau, by today's standard!)...is the one I still reference the most (it is the core of Tableau, IMHO...it was the genius idea that made Tableau 1.0 so an awesome bit of software, and is still the basic principle about how you think about turning a viz in your head, to rendered in Tableau...even in Tableau 10.0!!)

               

              Blue things and Green things - The Information Lab

               

              In short "Blue pills create headers, and Green Axis"..and that's it....if you just know this about Tableau you're 80% the way there!!...the best ideas are always the simplest!

              • 4. Re: Sorting a table based on most recent month over month difference
                msa s

                Hi simon

                 

                Simon Runc

                //

                [Month over month diff sales - Last Month SORTER] should be like this  ???

                 

                 

                 

                 

                this shuld be the formulae right ,like mentioned in above pic ,Please correct if i am wrong.

                lookup (zn(sum([sales])),-1)  this one is for sum of sales for previous month ?

                why we use last month sorter here ?

                • 5. Re: Sorting a table based on most recent month over month difference
                  Simon Runc

                  hi msa s,

                   

                  So if we use the ZN(SUM([Sales])) and/or LOOKUP(ZN(SUM([Sales])),-1) then the values these 2 formulas take change month by month...which is what the colouring ZN(SUM([Sales])) - LOOKUP(ZN(SUM([Sales])), -1) formula we have. If we use this, and as a discrete field, each Month takes a different value, and so we get multiple entries per customer...If I use this version of the formula (and restrict the view down to one customer) you'll see what I mean

                   

                   

                  There is a value for Last() is 1,666....so Last - November (LOOKUP(-1)) is 0-1,666 = -1,666 and for October Last() is still 1,666 and LOOKUP(-1) is 1,794 (so 1,794-1,666=128)...and as we're using them as discrete both values will create a row (for that customer).

                   

                  By hard-coding Last() and Last()-1 every value for a customer (regardless of the month) is the same calculation, so we just get the one value for every row for Amy Cox

                   

                   

                  (btw in this one customer example I had to add the 'Show Missing Values' else it will just take Dec from Oct....in the OP, as all customers are in view we have all months populated so the Last()-1 always looks at November).

                   

                  not sure I've explained that the best way!, but hope that sheds some light on why we've done it this way? Let me know if not, and I'll attempt to explain it differently/more-clearly!!

                  • 6. Re: Sorting a table based on most recent month over month difference
                    Mathijs Versteeg

                    So simple and elegant. Thanks alot! Exactly what I was looking for.

                     

                    Bonus question: would it be possible to compare Year-over-year YTD differences?

                    e.g. for Chairs I'd want to compare YTD jan-july '13 to YTD jan-july '14 sales? However, I'd still only want to see the actual monthly sales.. Tough one.

                     

                    I'll spend some time this weekend to figure it out, I'll repost if I've got it!

                     

                    Thanks again.

                    • 7. Re: Sorting a table based on most recent month over month difference
                      Simon Runc

                      ...So love a good bonus question!! There are a few ways we can go about this and I'd also check out Rody's excellent workbook library on the subject Current vs Previous Period to Date Comparison

                       

                      I've approached this thinking we want exactly the same vizLoD as our example...and have used LoDs. It's a bit complicated, but the 'how it works' tab should shed some light

                      I've also restricted the data to only show up to October 2014 (else data goes to December and that's the one month this is easy!!). I've added this as a context filter, so our LoDs think that this is the last date in the data)

                       

                      So first I want to dynamically find the last month of data...so I use this formula

                      [Max Month of Data]

                      DATEPART('month', {MAX([Order Date])})

                       

                      btw {} with no FIXED is just the short-hand for a FIXED LoD where I have no dimension to run it over...i.e. the whole data set)

                       

                      I then use this, and a (similar) [Max Order Date Year - LoD] {MAX([Order Date Year])} to create my 2 YtD values (one for TY and one for LY)

                       

                      [Sales YtD - LY]

                      IIF(DATEPART('month', [Order Date])<=[Max Month of Data] AND [Order Date Year] = [Max Order Date Year - LoD]-1,[Sales],NULL)

                       

                      and

                      [Sales YtD - TY]

                      IIF(DATEPART('month', [Order Date])<=[Max Month of Data] AND [Order Date Year] = [Max Order Date Year - LoD],[Sales],NULL)

                       

                      notice the -1 on the LY version

                       

                      I then need to use these to create my YoY...however as you have Month in the VizLoD I need someway of getting a single value applied to all Months (I've opted for another FIXED LoD!) but we could do this with INCLUDE/EXCLUDE LoD, or Table Calcs)

                       

                      So I create my YtD var as

                      [Sales YtD var - by Customer]

                      {FIXED [Customer Name]:SUM([Sales YtD - TY])}

                      /

                      {FIXED [Customer Name]:SUM([Sales YtD - LY])}-1

                       

                      fixing it to customer name

                       

                      and then we reverse the sign (and make it discrete) to create our sorter!

                      [Sales YtD var - by Customer sorter]

                      ZN([Sales YtD var - by Customer])*-1

                       

                      Hopefully this makes sense (I've added some of these calcs to the tooltips to help the understanding), but please post back if not.

                       

                      btw I've split these formulas out to help the understanding, but could be nested into one or two in your final solution