Skip to main content
Announcements
Global Transformation Awards! Applications are now open. Submit Entry
cancel
Showing results for 
Search instead for 
Did you mean: 
inescastelhano
Partner - Creator II
Partner - Creator II

Colormix with different limits

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

1 Solution

Accepted Solutions
Clever_Anjos
Employee
Employee

You can adjust your values, making a linear regression

View solution in original post

9 Replies
Clever_Anjos
Employee
Employee

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
Specialist II
Specialist II

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
Partner - Creator II
Partner - Creator II
Author

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
Partner - Creator II
Partner - Creator II
Author

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

inescastelhano
Partner - Creator II
Partner - Creator II
Author

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

Clever_Anjos
Employee
Employee

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
Partner - Creator II
Partner - Creator II
Author

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

Clever_Anjos
Employee
Employee

You can adjust your values, making a linear regression

Clever_Anjos
Employee
Employee

PFA attached an example

Capturar.PNG