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.
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.
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)
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.
1 of 1 people found this helpful
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.