Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

dcd123456
New Contributor III

Problem with ColorMix1 function

Hello

I have a pivot table with two dimensions in the rows i have he ID, and in de colums i have the months of the year

i whant to set, for each row (id)  the background color of the cell in function of the value that is showed,  and so the user can see quickly in witch month

there are problems.

somenthing like this

ID=MonthName(FECHA)ene 2014feb 2014mar 2014abr 2014may 2014jun 2014jul 2014ago 2014sep 2014
0508 2562362892882332701259222
101 8110812977101932262076
0204 134125129102149154123987
0401 752363287443596763
0703 701191121021127161533
0202 1481065780877052904
0506 5660778688105119512
0501 82758376838068669
0206 7235423742681038721
0201 263636701331064946
0203 104493629423450462

in the background color of the expresion, i use the wizard of the color mix to generate the color funcion, and the wizard generate this expresion:

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

when i saw the result first , it seem ok , the cell that have a high value are red and the lower balue are whitem,

but  are wrong.

i have attached the example, in the first row, for example the september 2014 cell have a value of 22, (lower than the others cell of the same row) but the the color is RED .

it seem that with the expresion generated with the wizard the background color is calculated with the value of the complete colum instead of the complete row.

if i pivot the table and put the month in the rows and the id in the columns the colors are calculated right but this visualization is very confused for the user, the user need see id in the rows and the month in the colums.

anybody know how to use colormix to do this, and calculate the brightness of the color depending of the value of the cell in each row??

thanks in advance for your help

regards

1 Solution

Accepted Solutions
MVP & Luminary
MVP & Luminary

Re: Problem with ColorMix1 function

Try replacing 'top' with 'first' and replacing 'NoOfRows' with 'NoOfColumns'


talk is cheap, supply exceeds demand

View solution in original post

3 Replies
MVP & Luminary
MVP & Luminary

Re: Problem with ColorMix1 function

Try replacing 'top' with 'first' and replacing 'NoOfRows' with 'NoOfColumns'


talk is cheap, supply exceeds demand

View solution in original post

dcd123456
New Contributor III

Re: Problem with ColorMix1 function

Thanks, it works.

regards

dcd123456
New Contributor III

Re: Problem with ColorMix1 function

one question, it is any diference between use the funcion first or last?

regards