Multiple If statements not returning correct result in Expression

[Peter_Brunner]

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

I

[Or]

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.

[Peter_Brunner]

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

[Peter_Brunner · Accepted solution]

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