topic Re: Expression not working as expected in QlikView

Expression not working as expected

soniasweety — Sat, 16 Nov 2024 21:50:48 GMT

I have used below logic but its showing me wrong result,

how to frame this logic,

logic:

<97 or >103--red

100-green

>=97 and <100 or >100 and <=103 --yellow

used Expression: Expression says ok but result getting wrong colurs

=IF(Num([Rate],'#,##0.00%')<'97.00%' and Num([Rate],'#,##0.00%')>'103.00%',red() ,
IF(Num([Rate],'#,##0.00%')>='97.00%' and Num([Rate],'#,##0.00%')<'100.00%' and
Num([Rate],'#,##0.00%')>'100.00%' and Num([Rate],'#,##0.00%')<='103.00%',Yellow(),Green()))

Re: Expression not working as expected

neelamsaroha157 — Wed, 21 Nov 2018 18:04:38 GMT

Understanding that you want to have red color when the value is greater than 103%, yellow if value is between 97% & 103% and green if the value is less than 97% -

Try this -

if(Num([Rate],'#,##0.00%')>'103.00%',red(),

if(Num([Rate],'#,##0.00%')>'97.00%',Yellow(),
Green()))

I would also suggest to use the absolute numbers like .97 for 97%, it just makes the code look neat.

Re: Expression not working as expected

Colin-Albert — Wed, 21 Nov 2018 18:09:04 GMT

Your ands and ors are mixed up.

Try this

=IF(Num([Rate],'#,##0.00%')<'97.00%' or Num([Rate],'#,##0.00%') >'103.00%',red() ,
IF(Num([Rate],'#,##0.00%')>='97.00%' and Num([Rate],'#,##0.00%')<'100.00%', Yellow(),
IF(Num([Rate],'#,##0.00%') >'100.00%' and Num([Rate],'#,##0.00%') <='103.00%', Yellow(),

Green() )))

The rate cannot be <97% and > 103%

Re: Expression not working as expected

soniasweety — Wed, 21 Nov 2018 18:07:56 GMT

No.
i want to show
1. exact 100% means green
2. <97 or >103 red
3. >=97 and <100 or >100 and <=103 --yellow

Re: Expression not working as expected

soniasweety — Wed, 21 Nov 2018 18:11:38 GMT

thanks colin .

but still 99.99% showing green only its should be yellow

Re: Expression not working as expected

neelamsaroha157 — Wed, 21 Nov 2018 18:12:23 GMT

Okay, then try this -

if(Num([Rate],'#,##0.00%')='100%',Green(),

if(Num([Rate],'#,##0.00%')>'103.00%' OR Num([Rate],'#,##0.00%')<'97.00%',red(),
Yellow()))

Re: Expression not working as expected

Colin-Albert — Wed, 21 Nov 2018 18:13:18 GMT

Your expression is based on 2 decimal places so is actually calculating as

1. exact 100.00% means green
2. <97.00 or >103.00 red
3. >=97.00 and <100.00 or >100.00 and <=103.00 --yellow,

so 100.01% will show as yellow - Are you sure you are working to this level of precision?

Re: Expression not working as expected

soniasweety — Wed, 21 Nov 2018 18:14:39 GMT

Yes its exactly 100% means it should show green

Re: Expression not working as expected

soniasweety — Wed, 21 Nov 2018 18:17:43 GMT

your expression is showing 100.00% also yellow

Re: Expression not working as expected

soniasweety — Wed, 21 Nov 2018 18:20:28 GMT

hi Colin_albert

Neelamsaroha157

atatching the sample

Re: Expression not working as expected

Colin-Albert — Wed, 21 Nov 2018 18:22:41 GMT

Can you display the percentage value with more decimal places to see if there is a rounding issue causing the green/yellow problem.
I would add this as another column for testing without the colour expression.

Or change one of the yellow() options to another colour to see which logic path is being followed.

Re: Expression not working as expected

soniasweety — Wed, 21 Nov 2018 18:24:59 GMT

i have attached sample could you please help on this?

Re: Expression not working as expected

soniasweety — Wed, 21 Nov 2018 18:30:10 GMT

i changed yellow to other colour... its still not showing yellow colour. only green and red showing,

Re: Expression not working as expected

Colin-Albert — Wed, 21 Nov 2018 18:33:14 GMT

The yellow/green issue is due to rounding. See attached and Match Rate2 column.

Re: Expression not working as expected

soniasweety — Wed, 21 Nov 2018 18:35:11 GMT

thanks,but how can i show the same format? as original column?

Re: Expression not working as expected

Colin-Albert — Wed, 21 Nov 2018 18:40:17 GMT

Your load script has the expression

Num([Net Sales Order US Dollar Amount Yotta]/[Net Sales Order US Dollar Amount CDR], '#,##0.00%') as [Match Rate],

NUM is a number formatting function and does not round the values, so Qlik still holds the Match Rate value with full precision.

Use Round() and your chart will work correctly.

Num(round([Net Sales Order US Dollar Amount Yotta]/[Net Sales Order US Dollar Amount CDR], 0.01), '#,##0.00%') as [Match Rate],

Re: Expression not working as expected

soniasweety — Wed, 21 Nov 2018 18:42:39 GMT

can i use this in chart expression or in script?

Re: Expression not working as expected

soniasweety — Wed, 21 Nov 2018 18:46:23 GMT

could you please help on this?

how can i get filter as three colurs and how to incorpate while selection?

https://community.qlik.com/t5/New-to-QlikView/Modify-the-logic/td-p/1510519

Re: Expression not working as expected

Colin-Albert — Wed, 21 Nov 2018 18:47:01 GMT

Either. I would say that rounding the values in the script would be simpler as any other expressions based on the Match Rate will all use the same precision.

Num alters how the number is displayed, but keeps the actual number stored in Qlik unchanged. So with a % format, the number is stored as a decimal but displayed as a % and multiplied by 100 - so 0.1 shows as 10%

Re: Expression not working as expected

rwunderlich — Wed, 21 Nov 2018 18:52:45 GMT

I think you are making it more complicated than necessary by trying to test string formats. How about:

=if([Match Rate] = 1, green()
,if([Match Rate] < 0.97 or [Match Rate] > 1.03, red()
,yellow()
))

-Rob