Heatmap - Changing the background color based on the rank in that row

[#1]

Hi,

i am trying to craete a kind of heatmap where the maximum value in a row would be highlighted in dark blue (background color of that cell), the second highest should be highlighted in shade lighter then the 1st one, the 3rd higest value should be highlighted in a ligher blue shade coampred to second one and so on.

i am atatching the sample qvd.

I would be greatful if someone can do this for the first 2 rows.

the color codes is as follows:

	r	g	b
color for max value	112	48	160
color for 2nd max	75	172	198
color for 3rd max	219	229	245

Thanks a ton in advance.

[Nicole-Smith · Accepted solution]

See the attached.

[#3]

Thanks a ton...You Rock

[#4]

Hi Nicole,

Actually in my actual data, i have 9 columns. I understood the logic for 3 calculated dimensions. Suppose i have 2 other colums 'Server' & 'Accesories'. Then can you modify the expression only for the monitor. I will replicate the same for others. Is there any other easier method since as i just mentioned there would be 10 calculated dimensions.

[Nicole-Smith]

Would there still only be 3 colors?  Or will there be 9?

[Nicole-Smith]

For the example that you gave me with 5 columns, I made the chart with 5 different colors in the attached.  I know they're not the colors that you want, but you should be able to easily replace them.

**EDIT: I should also mention that I needed to use a pivot table instead of the original straight table that I had given you in order to get this to work in this way.

[Anonymous]

Yousuf

If you use a Pivot Table with ProductsA as a dimension, then you only need one expression that exploits the Rank() function which will make things easier to maintain.

See attached qvw.

Best Regards,    Bill

[#8]

Thanks for your help and prompt reply. This worked like a charm. I just have one final query.

How to change the text color based on the background color. for eg: If the background color has rgb(0,0,128) then the text color should have rgb (255,255,255)

Thanks a ton

[#9]

Hi Bill,

I see the same color twice in some of the rows in your version. So, i believe something is missing. Nicoles version is working perfectly fine.

Anyways, thanks for your time.

[Nicole-Smith]

I'm actually doing that in the second example that I sent you.  You just need to place the text color that you want in the same spot of the pick() as the corresponding background color: