topic AW:Re: AW:change color conditionally in table box in QlikView

change color conditionally in table box

Mon, 05 Jul 2010 14:52:38 GMT

Hello,

I need to change the colour of the values I've put in a table box if they are greater/ less than a certain value.

One way that I though would do this was to write the line below in "Calculation Conditions", where OS1 is the name of one of the columns in the table:

if ( OS1 < 7, red() )

But nothing happens 🙂 Could you give some hints on what I need to do?

Thanks alot,

Naeemeh

AW:change color conditionally in table box

brenner_martina — Mon, 05 Jul 2010 15:04:34 GMT

Hi, naeemeh

you have to put this expression in the Text or Background color by opening the expression with the PLUS-button or you can enter a fix value in Visual Cues

AW:change color conditionally in table box

Mon, 05 Jul 2010 15:17:50 GMT

Hi Martina,

Thanks for the reply. Could you explain it a bit more?

Where exactly do I need to go to write this expression for Text or Background Color?

Where is Visual Cues?

Thanks,

Naeemeh

AW:Re: AW:change color conditionally in table box

brenner_martina — Mon, 05 Jul 2010 15:59:06 GMT

Hi, naeemeh,

look at the example, the Visual Cues is the 6 Tab in properties

AW:Re: AW:change color conditionally in table box

Mon, 05 Jul 2010 18:34:23 GMT

Thanks a lot Martina.

I was using a table box before, so I changed it to a Straight table in Chart object and I found the stuff you were talking about

There is another problem now! My table looks like this:

TestName OS1 OS2 OS3

Test1 val1 val2 val3

Test2 val1 val2 val3

Test3 val1 val2 val3

I need to compare (val1, val2, val3) in row Test1 with say 7, then (val1, val2, val3) in row Test2 with say 8 and so on.

I tried this in Text: if(FieldValue(OS1, 1) < 1000, lightred()) but this turns the whole column under OS1 to light red.

Can you help me with this?

Thanks,

Naeemeh

AW:Re: AW:change color conditionally in table box

Mon, 05 Jul 2010 22:10:23 GMT

Try with [OS1], [OS2] and [OS3] as references in the "Text color" or "Background color" expression (Iassume that OS1,OS2 and OS3 are expressions too).

Let me know if it helps.

Cheers.

AW:Re: AW:change color conditionally in table box

Mon, 05 Jul 2010 22:24:25 GMT

Hey,

What do you mean by "Try with [OS1], [OS2] and [OS3] as references "?

Can you give me more details?

what I've done so far is :

if ( FieldValue ( 'OS1', 1 ) > 750 and RowNo() = 1, lightred() )

which works, but when I add the values for the other rows, like row 14:

if ( FieldValue ( 'OS1', 14 ) > 65000 and RowNo() = 14, lightred() )

nothing happens. For some reason it only works for row 1.

I think I need to iterate through the rows under each OS# and compare values, but I don't know how

I can do this in QLikView!!!?

I appreciate any hints and helps 🙂

Thanks,

Naeemeh

AW:Re: AW:change color conditionally in table box

Mon, 05 Jul 2010 23:06:07 GMT

You should try to associate a value to Test1, Test2 & Test3 as inline table for example (limittest).

Then you should use an expression on background color such as:

if([OS1]>limittest,lightred(),lightgreen())

I hope it helps.

AW:Re: AW:change color conditionally in table box

Mon, 05 Jul 2010 23:24:14 GMT

Thanks a lot for the replies.

What you suggested, makes the whole column of OS1 red. Let me explain what I need to do:

I have a straight table like this:

Test	OS1	OS2	OS3
A	6	7	18
B	5	8	17
C	4	9	16
D	3	10	15
E	2	11	14
F	1	12	13

I have put the Test field in Dimension and OS1, OS2 and OS3 fields in the Expression part.

Now I need to compare each cell under OS1 with a different value and change color. For example:

Compare OS1 at row 1 (which has a value of 6) to 7 and if it's less than 7, make it red.

Compare OS1 at row 2 (which has a value of 5) to 30 and if it's greater than 30, make it red.

Compare OS1 at row 23(which has a value of 4) to 9 and if it's greater than 9, make it red.

And so on to the last row. Then I need to do the same for OS2, 3, ......

What you suggested, compares the whole column to one value 🙂

Any suggestions?

Thanks,

Naeemeh

AW:Re: AW:change color conditionally in table box

Mon, 05 Jul 2010 23:30:32 GMT

But OS1, OS2, OS3 for Test "A" they all compare with 7, that's right?

AW:Re: AW:change color conditionally in table box

Mon, 05 Jul 2010 23:34:49 GMT

That's right

AW:Re: AW:change color conditionally in table box

Tue, 06 Jul 2010 13:25:49 GMT

You need to do something like this:


LOAD * INLINE [
 Test, TestMark
 A, 7
 B, 8
 C, 9
 D, 3
 E, 4
 F, 8
];

And then you have to write the expression to the background color as I told you before:


if (OS1>TestMark, LightRed())

Hope it helps!

AW:Re: AW:change color conditionally in table box

Tue, 06 Jul 2010 13:27:17 GMT

I forgot to tell you to do the same with the other background color expressions:


if(OS2>TestMark,LightRed())
if(OS3>TestMark,LightRed())

AW:Re: AW:change color conditionally in table box

Tue, 06 Jul 2010 14:56:27 GMT

Hey,

Thanks for the replies 🙂

I tried another way and it worked. I had 2 mistakes: one was FieldValue(). I used sum(OS1) instead.

And I used a series of if ... else ... then clauses.

if (sum(OS1) >= 750 and RowNo() = 1, lightred(), if (sum(OS1) >= 65000 and RowNo() = 14, lightred() ))

And it's working now 😄

Thanks a lot for your helps though

Naeemeh