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

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

Thu, 21 Apr 2016 06:16:12 GMT

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 ?

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

sunny_talwar — Thu, 21 Apr 2016 15:47:23 GMT

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

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

johnw — Fri, 22 Apr 2016 00:09:43 GMT

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)))

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

sunny_talwar — Fri, 22 Apr 2016 00:16:54 GMT

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))

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

Fri, 22 Apr 2016 04:32:11 GMT

Thanks John

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

johnw — Fri, 22 Apr 2016 15:51:27 GMT

Pretty!

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

johnw — Fri, 22 Apr 2016 23:33:07 GMT

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!

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

johnw — Fri, 22 Apr 2016 23:42:03 GMT

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.

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

sunny_talwar — Fri, 22 Apr 2016 23:57:33 GMT

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

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

johnw — Sat, 23 Apr 2016 00:03:46 GMT

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.

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

sunny_talwar — Sat, 23 Apr 2016 00:10:32 GMT

John that's the reason I still don't know how to use this. I just know it do some kind of color coding, but how it works it a mystery for me. I have to spend some time to understand some of the functionality such as Hierarchy, ColorMix.

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

ziabobaz — Mon, 27 Feb 2017 13:39:22 GMT

I am sorry, I have similar question. I use Sense though..

I have a Table with a Calculated Measure column, where a resulting value is from 0% to 100%.

1. Can i color code the cells based on their values? From Red (0%) to Green (100%), with a Yellow (50%) in the middle?

2. And, alternatively, Red would me the minimum of the selected, and Green for maximum of the selected.

Thank you!

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

sunny_talwar — Mon, 27 Feb 2017 13:43:31 GMT

What is your expression?

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

ziabobaz — Mon, 27 Feb 2017 14:01:01 GMT

I was trying to use colormix1

ColorMix1([FIELDNAME], RED(),GREEN())

But it won't allow me to use FILEDNAME, only the integer (from 0 to 1)

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

johnw — Mon, 27 Feb 2017 17:05:18 GMT

Assuming the functions work the same in QlikSense, you'd need colormix2() instead of colormix1(). Colormix2() requires a value from -1 to +1 and three colors. So you want an expression that maps your minimum selected value to-1, and your maximum to +1. I believe this should do the trick.

colormix2(2*([FIELDNAME]-min(total [FIELDNAME]))/(max(total [FIELDNAME])-min(total [FIELDNAME]))-1,red(),green(),yellow())

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

ziabobaz — Tue, 28 Feb 2017 09:22:35 GMT

it says "invalid FIELDNAME"

My FIELDNAME is a calculated dimension, I assume that is the reason

Unfortunately I can't calculate this field in Load Script because it involves set analysis

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

ziabobaz — Tue, 28 Feb 2017 10:01:01 GMT

I think i found a workaround of using a calculated field inside calculated field (or in a background formula).

I created a variable with the same formula as calculated field.

then a added this formula in the color box:

colormix1($(vSelloutPeriod),red(),green())

it worked!