Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
#1
Description | Amount |
Buy | -1,000 |
Sell | 600 |
Interest | 260 |
EMV | 0 |
-140 |
Want to return the sum of amount -140
#2
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
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
];
Hi,
Try below expression:
=Sum(if(match(Desc,'EMV') and Amount<=0,0,Amount))
Hope this helps.
Regards,
Andrei
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))
Hi,
Hm... in which type of chart are you trying to do so? Straight table? Text Object?
What dimensions are there?
Regards,
Andrei
hi,
check this
You can test both scenario by making change in inline table.
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
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
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
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
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
];