1 2 Previous Next 17 Replies Latest reply on Oct 18, 2013 10:38 AM by Steven Blanc

Constraining Colour to Each Row of a Table

Right,

So I've seen many conditional formatting samples on the forums and Jonathan in particular has provided a series of helpful workbooks.

However, I'd very simply like to colour code highest to lowest across each row of my table. I can't seem to figure this out, I'm assuming this should be very simple to do.

Any ideas?

Regards,

Steven Blanc

• 1. Re: Constraining Colour to Each Row of a Table

Hello Steven:

We'll need some more specifics, like highest to lowest what?  Can you post a packaged workbook, even a sample workbook that demonstrates the setup you'd use, and then tell us what you want color coded from highest to lowest?

Thanks!

• 2. Re: Constraining Colour to Each Row of a Table

Matthew,

I'm on the road already so I can't put together a sample but I don't think it needs a sample to explain.

I have a chart of sales. i.e. the values = sales

Across the top I have the salesperson ID. i.e. the columns represent sales by salesperson.

Down the side I have Item Categories. i.e. the rows represent the sales by category.

Sales1     Sales2         Sales3

Cat1     100           125               200

Cat2       34           180               160

Cat3       90             20                 10

I want to glance at each row and see the highest sales figure in green and the lowest in red. I can use the colour range so that those in between are yellow.

I hope that clarifies.

Cheers,

Steven

• 3. Re: Re: Constraining Colour to Each Row of a Table

I did find one complex example here:

http://community.tableau.com/message/139822#139822

However, I'm not sure that will be necessary--it depends on the complexity of your actual data.

In the attached, I set up a Window Sum on Sum(Sales), set to compute using Customer Segment.  Then, I created a stepped, diverging color scheme with 3 steps (since we have 3 columns).  The attached is the results.

1 of 1 people found this helpful
• 4. Re: Constraining Colour to Each Row of a Table

Matthew,

Now getting a chance to look at this example. It looks like just the trick. Going to test it out on my data now.

• 5. Re: Constraining Colour to Each Row of a Table

Great!  The key is the WINDOW CALC set to the dimension that sets up your rows, and the stepped diverging colors with as many steps as you have columns.

• 6. Re: Constraining Colour to Each Row of a Table

Well actually. I was thinking to leave it continuous as opposed to steps. The dashboard has a quick filter to add or remove salesmen at will. For swapping between all salesmen and salesmen by department etc.

Unless of course there is a way to code the number of steps to a parameter. But I don't think tableau supports that kind've tom foolery vba would let me get away with.

• 7. Re: Constraining Colour to Each Row of a Table

Yeah, I don't know of any way to parameterize that.  Continuous may or may not work well, it just depends on the data.

Cheers and let us know how it goes, and if you need more help.

• 8. Re: Constraining Colour to Each Row of a Table

Wait wait wait.

Your example doesn't actually work. It just changes the column colours.

I took another look at it, the top two rows look right, but the bottom two are off.

EDIT:

Actually the colour is based on the column total. Which is exactly what we're trying to avoid.

Cheers for the effort.

END EDIT.

Back to the drawing board?

• 9. Re: Constraining Colour to Each Row of a Table

I see that now, and certainly don't claim to be an expert.  I participate in threads like this to learn new skills myself.  I believe something similar to this method can be used, but it may require more tweaking.

I tried setting the table calc to restart every Customer Segment, but that didn't work either.  I swear I've seen something like this used before.

Pinging Joshua Milligan, Jonathan Drummey, Shawn Wallwork to see if they have a better approach (I am sure they will, or they'll be able to tell us we are wasting our time)

1 of 1 people found this helpful
• 10. Re: Constraining Colour to Each Row of a Table

I appreciate that, I do the same myself.

Hopefully one of these guys can help out. I wouldn't have thought this would be so difficult!

The first link you sent has it done by column instead of row, I'm going to see if that setup is manipulatable to get the desired effect.

• 11. Re: Constraining Colour to Each Row of a Table

I really don't know if you'll find a good solution.  This is an often requested feature.  I do believe you can encode columns based on a range of values, so perhaps if you could separate your sales into different calcs, you could get what you want.  If your production workbook is far more complex than the examples we've been working on, you're probably going to have a hard time finding a good workable solution.

• 12. Re: Constraining Colour to Each Row of a Table

It's effectively the same as your example. The only difference would be the ability to filter salesmen and the sales dates. Neither of which affect the method in any significant way.

Trying to figure out the other example and I can't really make heads nor tails of what they've done.

• 13. Re: Re: Constraining Colour to Each Row of a Table

See the attached. It uses an INDEX() table calc with an advanced Compute Using on Department, Customer Name (the stand-in for sales person), sorted by Sum/Sales/Descending, with a reversed red/black diverging color palette. I recommend against using a red/green palette because up to 10% of the population is color-blind, several % of them in the red-green.

Note that the Index calc includes cells where there is no sales (like Rosemary Hedrick/Office Supplies), and ignores ties. It's possible to write a calc that skips nulls (so a search for TCRL and Rank and you'll find several), or you could wait a bit longer for version 8.1 to come out and use one of the built-in RANK calcs that ignore Nulls and have a variety of possible tie-breaker options.

There are some other views, for example a treemap with brushing on the Color legend can work really well. I also set up a bar chart with a couple of different sorts. If you're looking to rank each salesperson across each department, you could also do something like a bumps chart (see Top 10 Tableau Table Calculations | Tableau Software for an example).

Jonathan

• 14. Re: Re: Constraining Colour to Each Row of a Table

Beautiful!  Thanks Jonathan for stepping up, once again!

1 2 Previous Next