1 2 Previous Next 15 Replies Latest reply on Jul 10, 2013 5:53 AM by eraufars

Combination of conditional colouring by several variables

I have learnt a few ways of doing conditional colouring, and I would like to combine them.

I have some items sorted by category and summed up, and if the items of one category pass by more then 75% then the category will PASS, otherwise FAIL (see first figure).

These categories are also color coded by category for easy identification (see second figure).

What I want to do in my display is combine these two, so that any category that passes the 75% mark will be shown in GREEN (the pass color), but if it fails, it will be shown in its own category color (third figure).

I just wish there was a script or something, I could code this in 1 minute, but I haven't found a way to do it with tableau's interface. It seems that for 1 figure I can only put 1 measure in the "color" box, putting another one will replace the first one so it only lets me do either, not a combination.

• 1. Re: Combination of conditional colouring by several variables

Hi Emil,

You should be able to do this with a calculated field. Not exactly a script, but probably what you need.

Something like

Color =

IF AVG([Pass%]) >= 0.75 THEN "Pass"

ELSE ATTR([Category])

END

Replace AVG([Pass%]) with whatever measure you have on the columns shelf. The AVG() function also makes this field an aggregate, which means [Category] has to be an aggregate. ATTR() is an aggregation function for dimensions that returns the dimension value if only one is present and "*" if more than one is present. You could also use MIN() or MAX(), but the ATTR() provides a bit of error checking, at the expense of efficiency since it's running a IF MIN([Category]) == MAX([Category]) THEN [Category] ELSE "*" END.

Jim

1 of 1 people found this helpful
• 2. Re: Combination of conditional colouring by several variables

I tried something similar to this but got the

Expression too complex in query expression

Error, researching that now. There's a chain of calculated fields that result in the values used for input, and it seems the DB cannot handle it.

I will try what you suggested, and I will report back.

• 3. Re: Combination of conditional colouring by several variables

Interesting. Definitely report back and if it doesn't work perhaps you can share the calculated field you're using along with the type of DB (or if it's a text / Excel file). ..

1 of 1 people found this helpful
• 4. Re: Re: Combination of conditional colouring by several variables

I followed your idea and made up some data to test with, so I wouldn't get the JET DB error of complexity, which is unrelated to the main point of my question.

It works in part.

For this data

category;value

a;60

b;80

c;95

d;42

I made a calculated value "color"

IF AVG ([value]) > 75 THEN 'pass'

ELSE ATTR (category)

END

It gives me the graph that I want, but there is still one problem.

The calculated value only provides ATTR() values for those categories that currently fail the test. So I can provide colors for them. I don't get the option to provide colors for the ones that pass the test though. See screenshot.

How to work around this?

Tried this too, same problem. Doesn't give the option to choose colors for those values that currently do not occur

IF [value] >= 75 then 'pass'

ELSEIF [category] = 'a' then 'a fail'

ELSEIF [category] = 'b' then 'b fail'

ELSEIF [category] = 'c' then 'c fail'

ELSEIF [category] = 'd' then 'd fail'

END

• 5. Re: Re: Combination of conditional colouring by several variables

Sounds like you want a different color for each pass/fail-category combination. You could do something like

IF AVG ([value]) > 75

THEN 'pass_' + ATTR(category)

ELSE 'fail_' + ATTR (category)

END

• 6. Re: Combination of conditional colouring by several variables

There's actually a much easier way to do this in Tableau v8 --- if you want to color all combinations.

Just select your pass/fail field and the category field (ctrl-click) and drag both to the color shelf. Tableau will combine them for you. You can also add them individually while holding down the shift key and dragging the fields to their the color button or the color legend.

Jim

1 of 1 people found this helpful
• 7. Re: Re: Combination of conditional colouring by several variables

It would not hurt to have that, but for in this case they will all have the same 'Pass'  colour. The user wants to end up with 'all green' indicating it is good.

What you wrote above is nice code but I strongly suspect it will produce the same problem as my example (updated reply). Tableau doesn't give me all the possible outcomes to choose colours for, only the current ones. What happens when I change the data?

• 8. Re: Re: Combination of conditional colouring by several variables

Again, thanks. The combination of two values into the color box is a huge step forward. I was wishing for something like that.

Still, I am not given the color options for all combinations, only for those that right now happen to be on the figure.

How can I set up the colors for all 8 combinations (in this case) so that changing / filtering / other operations on the data will all display as intended?

• 9. Re: Re: Combination of conditional colouring by several variables

If you want to set specific colors for categories, one option is to set the pass / fail to 100 so that all values fail and you see all of the categories in the legend. Then you can set the colors and Tableau will remember the palette for this field, and you can reset the threshold value. If categories fail in the future, you're previously selected colors will be used.

• 10. Re: Re: Re: Combination of conditional colouring by several variables

I added another data field "true" and more values so that I could filter on something to change what displays in the table.

Now I can use this filter to switch the diagram and have each category pass or fail depending on the sum. When doing this, in each instance it will allow me to set the color for the current configuration, and it seems to remember what I chose so this looks like a working solution. It is very impractical though.

Why can't the "edit colors" window just allow me to choose values for all combinations right away?

Or, as asked for in the beginning, there could be an optional script interface to set things like this. That would be much preferred.

• 11. Re: Re: Re: Combination of conditional colouring by several variables

Thanks, your answer is more practical than the similar solution that I found about the same time.

Alright, this works. But still, there must be a more practical way to do it than 'fake' values just to get access to those color choices?

• 12. Re: Re: Re: Combination of conditional colouring by several variables

Not that I know of, but I usually hand select colors and I haven't really experimented much.

If I were doing this a lot (or needed to change colors on a bunch of different workbooks), I'd probably edit the XML files directly.

Tableau doesn't support this, but doesn't seem to strongly discourage it either, since the .twb files are easily understood XML. And, as you say, it wouldn't be difficult to write a script to add, edit or delete the xpaths. There's of course no public schema or documentation for the XML and no guarantee that it won't change in the future.

Jim

1 of 1 people found this helpful
• 13. Re: Re: Re: Re: Combination of conditional colouring by several variables

How about an alternative solution for identifying the marks that are passing the threshold? Using color to mean two different things can be a) confusing and b) more difficult for users who are color blind to interpret.

In the attached, I set up Shape Marks on a dual axis, and another that uses the Size Shelf to change the thickness of the non-passing bars.

• 14. Re: Re: Re: Re: Combination of conditional colouring by several variables

True --- especially since it sounds like Emil has a lot of categories and when you limit yourself to color blind safe colors, there aren't that many to choose from.

Perhaps even sorting by the value and having a dividing line between pass / fail or lightly shading the background could work.

1 2 Previous Next