6 Replies Latest reply on Oct 23, 2013 10:21 AM by Jonathan Drummey

# Highlighting Top and Bottom N

I've started this discussion so I can post a workbook in response to the idea here: http://community.tableau.com/ideas/2706

which is sparked by a capability of Spotfire in which one can create rules to highlight the top and bottom values using conditional rules (see Conditional Coloring to Highlight Top and Bottom Values - Tip of the Week).

So, is Tableau up to the challenge?

You bet!  I've attached the workbook and will make some time to give some details later on.

Regards,

Joshua

• ###### 1. Re: Highlighting Top and Bottom N

Very nice.  I might suggest this go into the TabWiki area, or anywhere where it can be found for easy accessibility.  Another example of where Personal Bookmarks would be useful (vote here: http://community.tableau.com/ideas/2695)

1 of 1 people found this helpful
• ###### 2. Re: Highlighting Top and Bottom N

It seems that this only works if the data includes all combinations of dimensions (every category has a state, etc.)  With sparse data, I run into problems.  See attached--is there any way to make it work for an example like this?

I'm just trying to learn, and decided to try and apply this to another data set and ended up with this result--if I use Continent instead of State, it will work perfectly because there's a Continent for each Category.

I've also attached Joshua's original with City in place of State, as another example.

• ###### 3. Re: Highlighting Top and Bottom N

The Top N part of the calc works because of how the data is sorted, the Bottom part doesn't in this case because it's going off the LAST() table calculation which is only looking at the rows in the partition and because of the table calculation the domain is being completed.

What we need is a "last N non-Null values" which there are solutions for (I can dig one up if need be), and at this point given we'll be seeing 8.1 soon we might as well use the RANK_UNIQUE() function instead (which is a lot easier).

Joshua Milligan, I suggest revising the solution to deal with sparse data. A couple other notes that are more user preference: In the table calc, you had At the Level = State, which given there is only one child level (State) is the same as the default Deepest. Also, if you used a continuous color with 3 segments (like -1 = bottom, 0 = neither, 1 = top) then you could use a Tableau heatmap instead of the BYO heatmap. (It's an oddity of Tableau v8 that when we have a discrete on color then we can't use the square mark for a heatmap, but we can use a continuous).

Jonathan

1 of 1 people found this helpful
• ###### 4. Re: Re: Highlighting Top and Bottom N

Jonathan & Matt,

Thank you for pointing out the issue with sparse data.  I'm starting to learn to take that into account .

My thought is to ensure that I don't have two dimensions on opposing shelves.  So in my re-worked example, I've moved Region to the Level of Detail and used ATTR(Region) on Columns.  That seems to turn off the domain completion.  Are there other recommended ways of doing this?

Jonathan, thank you also for the notes on table calcs!

Regards,

Joshua

• ###### 5. Re: Highlighting Top and Bottom N

This is all very interesting (and educational) to me.

• ###### 6. Re: Highlighting Top and Bottom N

That's the best way I know of to avoid domain completion.

On Wed, Oct 23, 2013 at 12:10 PM, Joshua Milligan <