13 Replies Latest reply on Feb 28, 2018 7:02 PM by Sriram Chandra

# Conditional Format - Multiple criteria / colors (cell background) - (.twbx Attached :)

Hi everyone,

It's almost 3 am and I've been trying for several hours to solve this on my own without any luck.  Therefore I'm sending out the Bat Signal to all tableau wizards, zen masters and Jedi's!

I'm trying to do a Heat Map that will color code cells based on their values.   Not sure how to write the calculation that will allow the table to color code (cell background) based on a SIX different colors (risk levels).  To complicate matters, the criteria used to color code is segmented by 3 different variable types, ( aka 'Vintage (A)' , 'Vintage (B)', and 'Vintage (C)' ) ,  that use their own specific criteria to assign the risk level (color).

Hopefully what I wrote makes sense.  To help explain, here's the legend that the criteria is based on:

Note:  For Risk Level-1, color coded based on < less-than value shown in legend,  and for all other risk levels, color code based on greater than or equal to for value shown in the legend.

FINALLY, here is what the end result of the Risk Level Heat Map should look like in Tableau.  This was done in Excel but trying to accomplish this in Tableau.

Hopefully one of you talented guys can help me with this.  It would be greatly appreciated!!  I am attaching the Tableau workbook along with the table created in Excel for reference.

- Dario

PS - I'm CC'ing Ryan Slagle who is an absolute beast with Tableau and conditional formatting.  I'm hoping you get a chance to tackle this one!

Also CCing Pooja who is another beast in her own right     thank you guys!!

• ###### 1. Re: Conditional Format - Multiple criteria / colors (cell background) - (.twbx Attached :)

Hello Dario,

Please read these two articles (if you haven't already). I guess this is what you are looking for.

Regards,

Ramesh

• ###### 2. Re: Conditional Format - Multiple criteria / colors (cell background) - (.twbx Attached :)

Something like this?

The keys here are a Level of Detail (LoD) calculation for the Total Vintage and using the Variable as a dimension on the Rows shelf, rather than Measure Names/Measure Values. Hopefully you can see the rest in Sheet 3 and the calculated fields it uses in the attached example.

2 of 2 people found this helpful
• ###### 3. Re: Conditional Format - Multiple criteria / colors (cell background) - (.twbx Attached :)

Bill,

THANK YOU!!!    I just got home and went straight to my computer to see if there were any responses..  and there it is.. your beautiful screen shot doing exactly what it's supposed to do.

I'm going to download your workbook and check it out to see if everything is working but LOOKS LIKE YOU GOT IT !!

I owe you a beer !

Thank you guys for always coming through on the forums!

Dario

• ###### 4. Re: Conditional Format - Multiple criteria / colors (cell background) - (.twbx Attached :)

Bill,

I appreciate the solution you gave me which works perfectly for the workbook I originally posted, but when I tried applying the solution, I realized that the data structure wasn't correctly replicated (to my work-place .twbx).  Because of this, I wasn't able to apply the solution to my work-place exercise.  My work-place workbook has additional dimensions that need to be filtered out for the Calculation you created "Value % of Total".  I also think that the additonal dimensions has an impact on the LOD that you created.

I am uploading a new workbook which has the correct data structure.  I'm hoping you can help me finally solve this puzzle which I've worked all weekend without any luck.  I'm pretty sure based on your original answer, this should be an easy fix for you.

I'm hoping you can find a moment to help me out again.  Not being able to do this on my own is so frustrating.. Thank God for the people here who are willing to help

Attached is the new workbook and data set in case you need it.  Also here's an updated screen shot of what the end result should look like:

----

If anyone else wants to take a shot at this, Bill has laid out most of the solution, so would greatly appreciate ANY feedback

1 of 1 people found this helpful
• ###### 5. Re: Conditional Format - Multiple criteria / colors (cell background) - (.twbx Attached :)

Bill,

I figured it out!  Thank you for the leading me the way.  I kept working on trying to overcome the issues mentioned in my post above and finally cracked the riddle with a IF OR Statement.  Here's the Calc I created named "Vintage (ABC)

IF [Variable] = "Vintage (A)"

OR  [Variable] = "Vintage (B)"

OR  [Variable] = "Vintage (C)"

THEN [VALUES]

END

I can finally sleep easy now that this has been solved   Attached is the updated workbook with the solution.

1 of 1 people found this helpful
• ###### 6. Re: Conditional Format - Multiple criteria / colors (cell background) - (.twbx Attached :)

Sorry I didn't get back to you sooner, but I am very glad you figured it out on your own! Lessons learned through your own effort are always the most satisfying and valuable in the end.

1 of 1 people found this helpful
• ###### 7. Re: Conditional Format - Multiple criteria / colors (cell background) - (.twbx Attached :)

I'm back Bill and anyone else following along.  I'm having issues implementing the formulas outlined above particularly with the LoD  formula.   Something unusual occurs when I apply it to my actual work-place example.  The ratios shown in the above HEAT MAP post, are based on a simple formula that uses a LOD Calc. that provides the denominator in the ratio formula.  In the above post, I replicated the numbers, measures, calculations, and dimensions in the TWBX workbook and gives me the correct answers.  Yet when I copy the exact calcs and LOD formula to my work-place workbook, I am getting incorrect results.  After digging deeper, I saw that the LOD was giving me the wrong denominator values on certain months and I simply can't understand why.  The LOD value should always be the same as such (from the example workbook that functions correctly:

However, in applying the same exact Calcs. and replicating the workbook used from this forum that functions correctly, I see that the LOD is returning this in my work-place example.  What's most unusual is the pattern (highlighted in orange).  The values should all be the same across the board yet it is stopping at certain different ranges, creating a pattern.  (only the months August through December seem to be calculating correctly.  See Below:

The solution to this problem my be difficult or very obvious to you Zen Masters.  I'm hoping the latter, where I can finally put this issue to bed and implement the Heat Map soon into my work-place environment.

May the force be with you all who try to crack this riddle

THANKS TO ALL WHO ARE ABLE TO PARTICIPATE !!

ps:  Sorry that I didn't upload the work-place workbook, but the example workbook in the posts above is the exact same data set, and same structure in regards to dimensions, measures, and Calcs.

• ###### 8. Re: Conditional Format - Multiple criteria / colors (cell background) - (.twbx Attached :)

You say, "the example workbook in the posts above is the exact same data set, and same structure in regards to dimensions, measures, and Calcs," however, that is clearly not true, since your screen shot shows a pill for [Sub Categoria O Cartera], which does not exist in the data set of your prior example. Without that, nobody can replicate your problem. Without replicating your problem, a definitive solution is not possible.

Here are some possibilities I can think might contribute to your problem:

• Your LoD is FIXED on [DATE], [Country], [Product Type], but [Product Type] is not in the view. If different product types exist in different months, that might cause different results. Removing [Product Type] from the LoD may resolve this.
• Your sample has only one date per month, so the [DATE] in the LoD resolves to only one value per month. But if your production data has dates for other days in the month, then the LoD would need to change accordingly. Since you can't currently put a formula in the LoD field list, you would need to create a calculated field or a data-part field which resolves to the year and month.

I hope this helps.

1 of 1 people found this helpful
• ###### 9. Re: Conditional Format - Multiple criteria / colors (cell background) - (.twbx Attached :)

Hi Bill,

Thank you for response and will take your advice on protocol.  Was trying to get eyeballs on my post knowing that it can easily be buried in a sea of forum posts.  Also in regards to the structure, it is in fact the same.  I changed the names of the dimensions which are originally in spanish.

I'm going to check the items you referred to that may be giving me trouble and see if I can find the solution on my own.

Appreciate all the time you have spent on my post.

All the best.

Dario

• ###### 10. Re: Conditional Format - Multiple criteria / colors (cell background) - (.twbx Attached :)

Well Bill, just one final thank you.

The items you had me check actually fixed my problem!  I had an additional dimension in the LOD that wasn't part of the view.

• ###### 11. Re: Conditional Format - Multiple criteria / colors (cell background) - (.twbx Attached :)

1 of 1 people found this helpful
• ###### 12. Re: Conditional Format - Multiple criteria / colors (cell background) - (.twbx Attached :)

This has changed my life!! We have several project dashboards that are heat colored in the background.  Now, I am able to complete my transition out of excel.  < I am so doing a happy dance.>

Of course, there is still the ability to export directly into a PowerPoint or link to one would make my life perfect.  We have some folks that are PowerPoint dependent.  I hope there is a reliable work around developed soon.

• ###### 13. Re: Conditional Format - Multiple criteria / colors (cell background) - (.twbx Attached :)

This will really helps a lot!

However If any of the cell has no value and if we want to color with Gray. Is it possible??