topic Re: Multiple If statements not returning correct result in Expression in QlikView

Multiple If statements not returning correct result in Expression

Peter_Brunner — Thu, 14 Jul 2022 05:00:28 GMT

Hi All,

I am getting erratic results as I have been trying to get an Expression with multi If statements running to return correct labels to what a Maximum Qty is to a Formula. I am not sure if there is another way to handle this as there is no between function in Qlikview

I have attached a small sample with my structure basically the if statements have to return 4 labels

'IF (MaxQty>=(SALES*1.4),"MAX OVER",IF(MaxQty<(SALES*0.5),"MAX UNDER", "OKAY") else if error "MAX_CHECK"

the Variables 1.4 and .5 to create a buffer for MaxQty to fall between.

any Help Much Appreciated

regards Peter

Re: Multiple If statements not returning correct result in Expression

Or — Thu, 14 Jul 2022 14:03:14 GMT

Your sample data and expected output doesn't seem to match your formula.

For ID1, MaxQty is 10, Sales*vMaxUnder/vAveMth is 15.5

15.5 is not less than 10, so this should not return MAX_UNDER as per Sales*vMaxUnder/vAveMth < MaxQty

For ID4, MaxQty is 2, Sales*vMaxUnder/vAveMth is 1

1 is not greater than 2, so this is not OKAY as per Sales*vMaxUnder/vAveMth >MaxQty

You should be able to basically use the formula you posted here, replacing the hard coded figures with the variables, except I'm not sure what "if error" is supposed to mean as there is no possible error here - all values are caught in one of the three existing outcomes.

Re: Multiple If statements not returning correct result in Expression

Peter_Brunner — Thu, 14 Jul 2022 21:21:11 GMT

Hi,

First thanks for reply

I am looking at adjusting our MaxQty number if it does not fall between the 2 Sales Numbers

ID 1 the MaxQty is 10 the measures 15.5 is the Under and the 43.3 is Over

the MaxQty should fall between these 2 numbers = Okay

if in this case the MaxQty 10 is Under 15.5 which then = Under

if the MaxQty is 50 then it is = Over

ID 4 should = Okay

sorry , I hope this explains it better

regards Peter

Re: Multiple If statements not returning correct result in Expression

Peter_Brunner — Fri, 15 Jul 2022 05:33:18 GMT

I have found the solution see attached file it seems there is a certain order the if statements must be to get correct results

thanks

regards Peter