topic Re: Colormix with different limits in QlikView

Colormix with different limits

inescastelhano — Fri, 30 Sep 2016 10:08:42 GMT

Hi all,

I have a field which min and max values are not defined. I can have a min of -3000% and a max of 3% (because it's a variation). I would like to use colormix to color my field in the table, but I don't know how to do it.

I would like to have the following:

Between the min(Field) and 0 - Red to white

Between 0 and the max(Field) - white to green.

Could somebody help me please?

ColorMix1 ((1+Sign(2*(Field-RangeMin (top(total Field,1,NoOfRows(total))))/(RangeMax (top(total Field,1,NoOfRows(total)))-RangeMin (top(total Field,1,NoOfRows(total))))-1)*Sqrt(Fabs((2*(Field-RangeMin (top(total Field,1,NoOfRows(total))))/(RangeMax (top(total Field,1,NoOfRows(total)))-RangeMin (top(total Field,1,NoOfRows(total))))-1))))/2, ARGB(255, 255, 0, 0), ARGB(255, 0, 255, 0))

but in this case, if my values go from -3000% to 3%, my white will be -1500%, and I don't want that to happen.

Many thanks in advance.

Cheers,

Inês

Re: Colormix with different limits

Clever_Anjos — Fri, 30 Sep 2016 11:06:14 GMT

You should try Colormix2() because it has a "colorzero" parameter (white in your case)

https://help.qlik.com/en-US/qlikview/12.0/Subsystems/Client/Content/ChartFunctions/ColorFunctions/color-functions-charts…

Re: Colormix with different limits

zhadrakas — Fri, 30 Sep 2016 11:06:31 GMT

please try

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

replace value with your Fieldname.

Try the "Assistent for dynamic gradiant" to get the result you want.

You can find that if you are in edit expression window -> File -> Dynamic gradiant Assistent (or something like this)

regards tim

Re: Colormix with different limits

inescastelhano — Fri, 30 Sep 2016 14:00:47 GMT

This does not work because of this: the "middle" color will be the average of my two values, which does not work, because I need it to be zero....

Re: Colormix with different limits

inescastelhano — Fri, 30 Sep 2016 14:01:12 GMT

Thanks for your reply, but this expression does not work.

Re: Colormix with different limits

inescastelhano — Fri, 30 Sep 2016 14:02:35 GMT

I think that the if may not work with colormix...

Re: Colormix with different limits

Clever_Anjos — Fri, 30 Sep 2016 14:41:05 GMT

You said it would be zero

>>Between the min(Field) and 0 - Red to white

>>Between 0 and the max(Field) - white to green.

Re: Colormix with different limits

inescastelhano — Fri, 30 Sep 2016 14:47:05 GMT

Colormix2 only works with values between -1 and 1, which is not my case...

Re: Colormix with different limits

Clever_Anjos — Fri, 30 Sep 2016 15:10:31 GMT

You can adjust your values, making a linear regression

Re: Colormix with different limits

Clever_Anjos — Fri, 30 Sep 2016 15:18:17 GMT

PFA attached an example