Skip to main content
Announcements
See what Drew Clarke has to say about the Qlik Talend Cloud launch! READ THE BLOG
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

If and statements in the expression

I am trying to write an if multiple and statement in the expression; for example:

sum(if([Item Ledger Entry.Entry Type]='0' or '2' or '4' and [Sheet4$.TTB Class]='Bottled' and [Item Ledger Entry.Document Type]='5',((Item Ledger Entry.Quantity])*[Proof Gallons]),0))

But its returning all values of the [Item Ledger Entry.Document Type] 5 or other values.  Is there an error in this expression?  This is one of 3 expressions in my Chart Pivot Table Object.  The other two expressions are working correctly; not sure what the issue is with this one?  Any help would be appreciated.  Thanks.

3 Replies
nagaiank
Specialist III
Specialist III

Try the following statement:

Sum(

If(Match([Item Ledger Entry].[Entry Type],'0','2','4') > 0

   and ([Sheet4$].[TTB Class] ='Bottled')

   and ([Item Ledger Entry].[Document Type] = '5'),

   ([Item Ledger Entry].Quantity * [Proof Gallons]), 0

)

Sokkorn
Master
Master

Or you can try this

Sum({$<[Item Ledger Entry.Entry Type]={0,2,4}, [Sheet4$.TTB Class] = {'Bottled'}, [Item Ledger Entry.Document Type] = {5}>} [Item Ledger Entry.Quantity]*[Proof Gallons])

Regards,

Sokkorn

Not applicable
Author

Thanks.  I tried those but they didn't work.  This expression ultimately worked:

sum(if([$Item Ledger Entry.Entry Type]='0' or [$Item Ledger Entry.Entry Type]='2' or [$Item Ledger Entry.Entry Type]='4' and [Sheet4$.TTB Class]='Bottled' and [$Item Ledger Entry.Document Type]='5',(([$Item Ledger Entry.Quantity])*[$Item.Proof Gallons]),0))