RANK for negative and positive numbers for colormix2

[ziabobaz]

Hi!

I want to color column in a table based on SIGN and VALUE using Colormix2.

I can't figure out how to calculate Rank for negative numbers and No of rows where values are Negative and Positive. All this values are inputs for Colormix2 function.

Please help

[ziabobaz]

Having slept with this over the night, worked out the solution

colormix1(
   rank(total fabs(column(X)))/NoOfRows(total),
      if(column(X)>0,green(),red()),white()
)

Cheers

[vikasmahajan]

PFA

Vikas

[ziabobaz]

thank you, but this does not take into account that positives should be gradient green and negatives - gradient red, with zero as white. I have posted the solution above and it works 

[ziabobaz · Accepted solution]

correction - it seems that Colormix1 does not support IF inside the function

if(column(1)>0,colormix1(
rank(total fabs(column(1)))/NoOfRows(total),
green(),white()
),


colormix1(
rank(total fabs(column(1)))/NoOfRows(total),
red(),white())

)

[Vaibhav2]

Thanks for this idea but the color from 0 to negative should go from white to red. To achieve this, the formula should be

if(column(1)>0,colormix1(
rank(total fabs(column(1)))/NoOfRows(total),
green(),white()
),

colormix1(
rank(total fabs(column(1)))/NoOfRows(total),
white(),red())

)