Expression not working as expected

[soniasweety]

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()))

[Colin-Albert]

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.

[soniasweety]

i have attached sample could you please help on this?

[soniasweety]

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

[Colin-Albert]

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

[soniasweety]

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

[Colin-Albert]

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],

[soniasweety]

can i use this in chart expression or in script?

[soniasweety]

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

[Colin-Albert]

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%

[rwunderlich]

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