topic Re: Need Help with IF AND statement in QlikView

Need Help with IF AND statement

Anonymous — Fri, 11 Sep 2015 13:54:55 GMT

Hi All,

I need some help with an IF AND statement for my chart expressions . I have two
fields where I want to sum the amount if it meets the criteria. To put it in
words, IF Description=”EMV” AND Amount=0, then Sum everything in the Amount field Else Description=”EMV” AND Amount >0, Sum
just the SELL field.

See below example.

Description	Amount
Buy	-1,000
Sell	600
Interest	260
EMV	0
	-140

Want to return the sum of amount -140

Description	Amount
Buy	-1,000
Sell	600
Interest	260
EMV	500
	360

Want to return just the Sell for 600

Any inputs will be greatly appreciated.

Thanks,

Frank

Re: Need Help with IF AND statement

crusader_ — Fri, 11 Sep 2015 14:08:42 GMT

Hi,

Try below expression:

=Sum(if(match(Desc,'EMV') and Amount<=0,0,Amount))

Hope this helps.

Regards,

Andrei

Re: Need Help with IF AND statement

Anonymous — Fri, 11 Sep 2015 14:52:01 GMT

Hi Andrei,

The AND part didnt works even if i change it to greater or less than zero. It goes to the else and return the Amount.

Let me know if you can help.

Sum(if(match([Description],'EMV') and Amount<=0,0,Amount))

Re: Need Help with IF AND statement

crusader_ — Mon, 14 Sep 2015 06:43:41 GMT

Hi,

Hm... in which type of chart are you trying to do so? Straight table? Text Object?

What dimensions are there?

Regards,

Andrei

Re: Need Help with IF AND statement

ankit777 — Mon, 14 Sep 2015 07:24:40 GMT

hi,

check this

You can test both scenario by making change in inline table.

Re: Need Help with IF AND statement

Anonymous — Mon, 14 Sep 2015 13:41:43 GMT

Hi Andrei,

It's a straight table with 3 dimensions. See below

Name	Description	Amount
Apple	Buy	-1,000
Apple	Sell	600
Apple	EMV	500 or 0
Apple	Interest	260

*** Example EMV - either 500 or 0

Sum(if(match([Description],'EMV') and Amount<=0,0,Amount))

I want to return the amount field with these 2 criterias.

If Descritpion= EMV and Amount = 0 then
Sum(Amount)

Elseif Descritpion= EMV and Amount > 0 then

Sum(Amount) - Only the SELL's (600)

End if

Hope i didnt confuse you. Many Thanks,
Frank

Re: Need Help with IF AND statement

Anonymous — Mon, 14 Sep 2015 13:50:05 GMT

Hi Ankit,

I did test it out, only works for one scenario. Only returns the Amount even if i change the EMV =0. Below if statement is what i want to accomplish (also, please see my recent post to Andrei).

If Descritpion= EMV and Amount = 0 then
Sum(Amount)

Elseif Descritpion= EMV and Amount > 0 then

Sum(Amount) - Only the SELL's (600)

End if

Sum(if(match([Description],'EMV') and Amount=0,0,Amount))

Your formula works if EMV >0 and return sum of Amount. Once i change the EMV value to =0 , still return sum of the Amount.

Many Thanks

Frank

Re: Need Help with IF AND statement

crusader_ — Mon, 14 Sep 2015 14:16:19 GMT

Hi Frank,

The second condition seems a bit confusing, but have a look on my expression:

if(match(Description,'EMV') and Amount=0, sum(TOTAL Amount), //Means sum(Amount) of all cells(rows)

if( match(Description,'EMV') and Amount>0,

sum(TOTAL Amount)-sum({<Description={'Sell'}>} TOTAL Amount) //Means sum(Amount) of all rows - (minus) Only the SELL's row.. If you need SELL's row only just drop first part before (including) minus

, sum(Amount)

)

Hope this helps or just shed light on the solution)

Regards,

Andrei

Re: Need Help with IF AND statement

Anonymous — Mon, 14 Sep 2015 18:40:07 GMT

Andrei, Thanks for your help!. I tweeted your formula a bit and still didnt come to a solution that i wanted. Let me know if you can take a look at my sample QVW.

I have a Straight Table where i use Name, Description and an expression with If statement. The formula works in a way when i click "Table" Name such as Apple or Orange. For instance, if i click Apple it returns 360 and Orange returns 700 (only Sell) which both returns are correct with this formula. But when i clear it, it shows a different numbers.

Also, how can i show Name level in straight table? I tried without the description, no data at all.

for example -

Name Amount

Apple 360

Orange 700

Many Thanks.

Frank

Re: Need Help with IF AND statement

Digvijay_Singh — Mon, 14 Sep 2015 20:25:13 GMT

Try the attached one, it works for both the condition with the sample -

Load * Inline [

Name, Description,Amount

Apple,Buy,-1000

Apple,Sell,600

Apple,Interest,260

Apple,EMV,200

];

Re: Need Help with IF AND statement

Anonymous — Tue, 15 Sep 2015 14:44:17 GMT

Digvijay , Ankit , and Andrei. Thanks all for the help!.