Colormix with different limits

[inescastelhano]

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

[Clever_Anjos]

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/co...

[zhadrakas]

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

[inescastelhano]

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

[inescastelhano]

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

[inescastelhano]

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

[Clever_Anjos]

You said it would be zero

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

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

[inescastelhano]

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

[Clever_Anjos · Accepted solution]

You can adjust your values, making a linear regression

[Clever_Anjos]

PFA attached an example