topic Re: Color when an undefined value is there in a cell in QlikView

Color when an undefined value is there in a cell

Mon, 11 Aug 2014 09:29:00 GMT

Hi,

I have a pivot table where expression C = A/B; when B=0, it shows '-'. Its perfectly fine.

I want background color of the cell based on values of C..

if C<20%, red,

if 20%<=C<25%, Amber,

if C<=25%, Green,

if Undefined, Yellow,

I used the following expression -

=if(C<.2,red(),if(C>=.25,green(),if((C>=.2 and C<.25),rgb(255,183,255),yellow()))).

Two problems I am facing :-

1. Amber color is not proper, actually for that I want to use {rgb(255,183,255) hsl(200,240,206)}...but, am not able to use both ?

2. For undefined values, its not putting in Yellow..

Any idea ??

Re: Color when an undefined value is there in a cell

manideep78 — Mon, 11 Aug 2014 10:34:16 GMT

Hi try this,

If(C<0.2,red(),if(C>=0.2 and C<0.25,RGB(255,191,0),if(C<=0.25, Green(),if(C='' or C=null(), Yellow()))))

If possible please attach the sample App

Regards,

Manideep

Re: Color when an undefined value is there in a cell

Mon, 11 Aug 2014 10:41:44 GMT

The Yellow stuff is not working..actually, the value is not null rather it is undefined and tht's why, its not getting the condition for Yellow..

Re: Color when an undefined value is there in a cell

Mon, 11 Aug 2014 10:45:28 GMT

Try like

if(C = '-',yellow())

--Singh

Re: Color when an undefined value is there in a cell

manideep78 — Mon, 11 Aug 2014 10:45:59 GMT

Can you please give me the sample App

Re: Color when an undefined value is there in a cell

Mon, 11 Aug 2014 10:49:46 GMT

Try putting Yellow on Else condition..

something like...
If(C<0.2,red(),if(C>=0.2 and C<0.25,RGB(255,191,0),if(C>0.25, Green(),Yellow()))))

or what Angad Singh suggested.

Regards,
Dawar

Re: Color when an undefined value is there in a cell

manideep78 — Mon, 11 Aug 2014 10:51:55 GMT

If(C<0.2,red(),if(C>=0.2 and C<0.25,RGB(255,191,0),if(C<=0.25, Green(),if(C='-' , Yellow()))))

If(C<0.2,red(),if(C>=0.2 and C<0.25,RGB(255,191,0),if(C<=0.25, Green(),Yellow())))

Re: Color when an undefined value is there in a cell

qlikoqlik — Mon, 11 Aug 2014 11:51:17 GMT

Please see below code I have used in the background of the expression

if((sum(ColorA)/sum(ColorB))<0.2,red(),
if((sum(ColorA)/sum(ColorB))>=0.2 and (sum(ColorA)/sum(ColorB))<0.25, rgb(255,183,255),
if((sum(ColorA)/sum(ColorB))>=0.25, Green(),
Yellow())))

Regards

Padma

Re: Color when an undefined value is there in a cell

Mon, 11 Aug 2014 16:30:47 GMT

Can u try in case of Pivot ?..Yellow stuff is still not working for me ..:-(

Re: Color when an undefined value is there in a cell

qlikoqlik — Wed, 13 Aug 2014 11:50:33 GMT

Hi Vimal

It is working for me on Pivot table as well

if you can give me some sample data on excel I can try

Thanks

Padma