6 Replies Latest reply on May 5, 2017 12:45 PM by Justin Larson

How to get colors for hi low on a specific column

Hello. I have this chart and I would like to get a high low on just the current year metric (not both) When I add into the colors mark it changes both years of data   Spreadsheet attached.

• 1. Re: How to get colors for hi low on a specific column

don't know if I follow what you're going for. Something like this?

the calculation I used is : if ISNULL(sum([ThisYearSales])) = false and (FIRST()=0 or LAST()=0) then 'Color' END

calculated along Month (or table down in this layout)

1 of 1 people found this helpful
• 2. Re: How to get colors for hi low on a specific column

Hi Justin. Let's say green is high and red is low for one column I'd like a color spectrum. I just want one column to be colored like that.

• 3. Re: How to get colors for hi low on a specific column

hmm. Ok.

The coloring is going to apply to anything that shares an axis, so in the case of of your layout, there is one "axis" created by the Measure Values pill. This means that any color effect you put on that axis will effect all marks on the axis. So one option is to treat values in current year differently, say by a calculation of

if ISNULL(SUM([ThisYearSales]))=false then sum([ThisYearSales]) else 0 END

This forces the value that creates a color to be 0 for all non-this year values, but the result is that the last year column just kind of shows as low values, and it dampens the effect of the diverging color on This year numbers. Or you could set the default to be dead center instead of Zero.

A crazier option is to color on discrete values that come out of a calculation. Then you can Treat Last year with it's own value, and create steps for This year. The downsize is you're going to be assigning each step  it's own color manually, it's not truly a graduated color system. Something like

if ISNULL(SUM([ThisYearSales]))=false then str(rank(sum([ThisYearSales]))) else 'Not This Year' END

which ranks this year's values, casts it to a string, and returns "Not this Year" if it's not this year:

The more preferable option to either of these would probably to find a layout that tells the story that you want with two separate Axis, but that may prove challenging in a table format.

Someone else may come up with a better answer, but hopefully this primes your gears on how you can use calculated fields to manipulate the cards.

• 4. Re: How to get colors for hi low on a specific column

What is this formula doing and where do I insert it? Particularly the first and last and 'color' part. Working through your other response right now. Thanks!!

the calculation I used is : if ISNULL(sum([ThisYearSales])) = false and (FIRST()=0 or LAST()=0) then 'Color' END

calculated along Month (or table down in this layout)

• 5. Re: How to get colors for hi low on a specific column

Ufff that second piece is complicated haha...but interesting. I'm a heavy excel user so I was just thinking of it like conditional formatting on specific columns in excel. Thanks.

• 6. Re: How to get colors for hi low on a specific column

Tableau and Excel are definitely different paradigms. It helps to think of Tableau like a very powerful pivot table, and throw everything else about Excel out the door in terms of translating concepts. The important thing with Tableau to understand is Level of Detail and the resultant Partitions, which you could consider zones on your visualization.

All of the sample formulas I provided would be put on the Color Shelf.

First() and Last() are window calculations: they look at the actual layout of the table to determine the value. First and Last return the number of cells between the current cell being evaluated, and the last cell visible on the table. The first cell will show 0, and then count up from there. Being window calculations, you can manipulate what order those are calculated along in terms of the table layout or in terms of dimensions in the view. So if you compute along table across, your first column would show zero, and count up across the table. If you compute along table down, the first row would return 0, and count up going down the table.

Best way to explore is to type in First() and drop it in the labels, you can preview what it's doing. Then mess with the table calculation setting (compute along, etc) to see what the effect is.

The idea in the calculation with first() and last() provided was something along the lines of "If the label value is not null, and it's either the first or last number on the table, color it differently than the other marks"

The other formulas similarly inspect the contents of the cell, then compute an alternate string or number which is used to control the color. Hope that clarifies a bit.

1 of 1 people found this helpful