change color text in table

[#1]

Hello everybody

I have a table with 5 coloms

the first 3 coloms A, B, C have value's the forth colom is SUM A,B,C and the fith colom is the average of A,B,C

I aksed before to get the values red/green if below or above average, see http://community.qlik.com/message/287181#287181 so that worked out fine

I use this

The Expression tab and click on the + in front of the expression A and click on Text Color. In the textbox for Definition put this expression:

if(sum(A)>=(sum(A)+sum(B)+sum(C))/3,green(),if(sum(A)<(sum(A)+sum(B)+sum(C))/3,red()))

But I want to make some more difference between the colors, no I have 2 only green if above average and red if below.

I want to have 5 colors and the first one if the value in A is less then 10% of the average, second if A = between 10and 30% of average, third if A is between 30 and 70% of the average, fourth if A is between 70 an 90% of average and the fitht if A is higher then 90% of average.

Help would be very usefull

grtz Ed

[#2]

Hello Ed,

try using this formula:

if( sum(A) <= ((sum(A)+sum(B)+sum(C))/3) * 0.1, rgb(255,0,255),
if( sum(A) <= ((sum(A)+sum(B)+sum(C))/3) * 0.3, rgb(255,255,0),
if( sum(A) <= ((sum(A)+sum(B)+sum(C))/3) * 0.7, rgb(0,0,255),
if( sum(A) <= ((sum(A)+sum(B)+sum(C))/3) * 0.9, rgb(255,0,0),
if(sum(A) > ((sum(A)+sum(B)+sum(C))/3) * 0.9, rgb(0,255,0) ) ) ) ) )

You just have to change the rgb-values to the ones you need.

[Miguel_Angel_Baeyens]

Hi,

Let's say you have the Average in column number 5 (fifth expression) counting from the left. Then, the condition to get colors I'd use is:

If(Column(5) < .1, Color1,
  If(Column(5) < .3, Color2,
    If(Column(5) < .70, Color3,
      If(Column(5) < .90, Color4,
        Color5))))

Note that If() conditions in charts may degrade performance with large data sets.

Hope that helps.

Miguel

[Gysbert_Wassenaar · Accepted solution]

Something like:

if(sum(A)<0.1*(sum(A)+sum(B)+sum(C))/3,green(),

if(sum(A)<0.3*(sum(A)+sum(B)+sum(C))/3,red(),

if(sum(A)<0.7*(sum(A)+sum(B)+sum(C))/3,blue(),

if(sum(A)<0.9*(sum(A)+sum(B)+sum(C))/3,yellow(),

magenta()))))

[#5]

Thanx again, expression worked out fine for me.

Bedankt en hopenlijk tot een volgende keer