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 2014 | feb 2014 | mar 2014 | abr 2014 | may 2014 | jun 2014 | jul 2014 | ago 2014 | sep 2014 |
| 0508 | | 256 | 236 | 289 | 288 | 233 | 270 | 125 | 92 | 22 |
| 101 | | 81 | 108 | 129 | 77 | 101 | 93 | 226 | 207 | 6 |
| 0204 | | 134 | 125 | 129 | 102 | 149 | 154 | 123 | 98 | 7 |
| 0401 | | 75 | 236 | 328 | 74 | 43 | 59 | 67 | 63 | |
| 0703 | | 70 | 119 | 112 | 102 | 112 | 71 | 61 | 53 | 3 |
| 0202 | | 148 | 106 | 57 | 80 | 87 | 70 | 52 | 90 | 4 |
| 0506 | | 56 | 60 | 77 | 86 | 88 | 105 | 119 | 51 | 2 |
| 0501 | | 82 | 75 | 83 | 76 | 83 | 80 | 68 | 66 | 9 |
| 0206 | | 72 | 35 | 42 | 37 | 42 | 68 | 103 | 87 | 21 |
| 0201 | | 26 | 36 | 36 | 70 | 133 | 106 | 49 | 46 | |
| 0203 | | 104 | 49 | 36 | 29 | 42 | 34 | 50 | 46 | 2 |
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
