
1. Re: Sorting a table based on most recent month over month difference
Simon Runc Jun 12, 2016 12:46 PM (in response to Mathijs Versteeg)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 unhidden 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.

Sorting On Last Month.twbx 470.2 KB


2. Re: Sorting a table based on most recent month over month difference
Michael Hesser Jun 12, 2016 1:04 PM (in response to Simon Runc)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 Jun 12, 2016 1:16 PM (in response to Michael Hesser)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 Jun 12, 2016 7:18 PM (in response to Simon Runc)Hi simon
[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 Jun 13, 2016 1:31 AM (in response to msa s)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 01,666 = 1,666 and for October Last() is still 1,666 and LOOKUP(1) is 1,794 (so 1,7941,666=128)...and as we're using them as discrete both values will create a row (for that customer).
By hardcoding 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/moreclearly!!

6. Re: Sorting a table based on most recent month over month difference
Mathijs Versteeg Jun 15, 2016 11:55 AM (in response to Simon Runc)So simple and elegant. Thanks alot! Exactly what I was looking for.
Bonus question: would it be possible to compare Yearoveryear YTD differences?
e.g. for Chairs I'd want to compare YTD janjuly '13 to YTD janjuly '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 Jun 16, 2016 10:33 AM (in response to Mathijs Versteeg)...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 shorthand 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