How about something like this:
Use this calc to get a difference: LOOKUP(SUM([Sales]),LAST()) - LOOKUP(SUM([Sales]),FIRST()) and put it on the color shelf.
Then just use a custom diverging palette.
Attaching 9.1 workbook
***When uploading a workbook it is extremely helpful for us that help to know which version you're using.***
slopelinecolor.twbx 1.2 MB
2 of 2 people found this helpful
First thing to say is...what a deceptively tricky challenge!...I'm also pinging Patrick Van Der Hyde as he was interested in the solution.
So I'll first run through my logic on this (I'll also add that I have used the arrangement of pills for this, and not the hard-coded 2013 & 2014 sales values)...
So first we want the RANK for each year...straight forward enough, and we can just use the RANK (as you have done). We then want to compare the 2 RANKs, so we need to use a LOOKUP to reference back to the previous year RANK, and so create our RANK difference. Now the problem with this, is that the 2014 year is fine as it can do the difference (looking back to the 2013 RANK), but the the 2013 marks reference back to 2012 (well tries to), but as it's filtered out are it comes up NULL...which means we get a Viz like this
where the colours bleed from one to another. So we also need to get the 2013 RANK to look forward, and know the 2014 RANK too!...suitably confused?!
So in Tableau I first create a calculated field for [RANK Sales]
and run this, compute using Sub-Category
I then need to pick up either the Previous RANK (for 2014), or the Next RANK (for 2013). To do this I use the following calculated field
[RANK Sales Previous/Next]
IF ISNULL(LOOKUP([RANK Sales],-1)) THEN
LOOKUP([RANK Sales],1) ELSE LOOKUP([RANK Sales],-1) END
The ISNULL is only returned for 2013 (as -1 back from there is NULL...as we have filtered out 2012)...I also need to employ the trick of having the [RANK Sales] and [RANK Sales Previous/Next] compute using different levels ([RANK Sales] by SubCat, and [RANK Sales Previous/Next] by Year. When we have 2 Table Calcs in a formula, we get this option to change how each run.
So I set [RANK Sales] to be
and [RANK Sales Previous/Next] to be
So we're nearly there!...
The final part is to bring it all together to get the [RANK Diff], which is the following calculated field
IF ISNULL(LOOKUP([RANK Sales],-1)) THEN [RANK Sales]-[RANK Sales Previous/Next]
ELSE [RANK Sales Previous/Next]-[RANK Sales]
Depending if it's 2013 or 2014 it needs to reverse what is taken from what!
We can then bring this onto the colour shelf, and I've set it up like the below
to get the 3 colours (btw I've added the Grey for no-change).
Attachment is in 9.2 (I have 8.2, 9.0, 9.2 & 9.3 on my laptop, so no 9.1 unfortunately!)...so you'll need to update your version to look at the solution (you may as well upgrade to 9.3!...it's really, really fast!!)
...I do have a nagging feeling that my solution is more complicated than it needs to be, but I've had a bit of a play and can't come up with a simpler one (as such I'm also going to put out a gentle ping to Yuri Fal [the man of elegant solutions!!]...Yuri, as per previous gentle ping, I have a working solution but if you have 5 mins, would be good to see if there is a simpler way)
Hope the above all makes sense, and does the job, but if not please post back...nice challenge
Thank you for the ping :-)
Made it with a nested Table Calc.
Not much simpler than yours, anyway ...
Please find the attached wb.
slopelinecolor_YF.twbx 1.3 MB
This does the trick. I fought with that lookup calc myself for hours and struggled with that bleeding orange/blue effect. That's when I finally gave up and posted it thinking that I was missing something simple & obvious. I guess it is a bit more complex than I had originally thought.
Thanks for the detailed description.
Many thanks for jumping in...
Very dapper solution!...I hadn't come across the SIGN function before (very good to know)...and nicely gets around my uglier change the order of taking one rank away from the other (depending if you are in 2013 or 2014).
If I could dress our solutions (and this extends generally to all our solutions)...I see yours in a dapper-fitted-Ted-Baker suit and mine more of a hoody-and-trainers affair!!
I find myself using SIGN() more often.
May be my customers want more of