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

[soniasweety]

thank you.. but other issue i got... that same combination i need to use other two measures also.. but its not working for that,

can u check this attahced

[soniasweety]

thanks rob.  it seems working  but i need to test with real data. 

can you help on this?

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

[soniasweety]

sorry rob... still same issue. 100% showing yellow

Rwunderlich

PFA.

[rwunderlich]

It looks fine to me when I open your example file. I see a mix of red, green and yellow for the [Match Rate] column text. 

-Rob

[soniasweety]

Yes but yellow values should not be 100.00%

[PradeepReddy]

this is number formatting issue, the yellow color showing for 100% because it internally 100.1234% or greater than 100%. You need to use either Ceil() or Floor() functions.

[PradeepReddy]

try something like this..
Match Rate: num(Ceil(Column(3)/Column(2),'0.01%'),'#,##0.00%')
Text Color:
IF([Match Rate] ='100.00%', Green(),
IF([Match Rate]< '97.00%' or [Match Rate]> '103.00%', Red(),
IF(([Match Rate]>='97.00%' and [Match Rate]< '100.00%') OR ([Match Rate]>'100.00%' and [Match Rate]<= '103.00%'), Yellow())))

[soniasweety]

thank you.. i will test this.

could you please look into this any help on this?

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