Different color shades between only two color depending on number of rows and Values in the cell

[#1]

Hi All,I have a requirement where I have to color the cell in different shades between two colors (say red and blue) based on the cell value and number of color shades also depend on no of rows like in the below image.

is there any way to achieve this ?

[sunny_talwar]

Have you looked into ColorMix Wizard (Qlikview ColorMix Wizard by RFB 169 - YouTube)

[johnw · Accepted solution]

The attached uses this background color expression. You'll want to play with it until you're happy with the colors, but I think it's what you're looking for?

=if(Margin>0,hsl(.35,.65,rangemax(.4,1-Margin)),hsl(.90,.80,rangemax(.4,1+Margin)))

[sunny_talwar]

John I stole your qvw to show colormix idea

ColorMix2 (if(Avg(Margin)<0,-Sqrt(-(Avg(Margin)-0)/(0-RangeMin (top(total Avg(Margin),1,NoOfRows(total))))),Sqrt((Avg(Margin)-0)/(RangeMax (top(total Avg(Margin),1,NoOfRows(total)))-0))), ARGB(255, 255, 0, 0), ARGB(255, 0, 255, 0), ARGB(255, 255, 255, 0))

[#5]

Thanks John

[johnw]

Pretty!

[johnw]

Though a simple colormix2(Margin,lightred(),lightgreen(),white()) does a very nice job on this data set. But he did talk about blending through the number of rows, where this just assigns colors to values. Anyway, somehow I'd missed the colormix functions. Thanks!

[johnw]

Sure! But colormix2() is a cleaner solution. It only looked complicated because Sunny T was trying too hard. Or maybe that's the output of the wizard. I can't seem to find the wizard. I feel like a newb. I need to watch the YouTube video when I'm not at work.

[sunny_talwar]

Once you go into the BackGround Expression window, you will see the color mix wizard under the File menu

[johnw]

Ah, thank you. So the complicated output is just what happens when you use the wizard, which is I guess why I never use wizards. Except for reading in Excel files because for whatever reason the few parameters you need for those are the ones I always seem to forget. And then once it gives me the parameters, I reformat it because I want my code to look just so.