Help in Qliksense set analysis for using if statement

[Rsaiq]

Hi,

I am trying to create an set analysis like below and it is working fine as i am trying run that expression only when we have version=0 & Account=Sales,Finance & Warehouse.

=num(RangeSum(Sum({$<VERSION={0},ACCOUNT={'Sales','Finance','Warehouse'}
>} amount1),
Sum({$<VERSION={0},ACCOUNT={'Sales','Finance','Warehouse'}>} amount2),
Sum({$<VERSION={0},ACCOUNT={'Sales','Finance','Warehouse'}>} amount3))/
Sum({$<VERSION={0},ACCOUNT={'Sales','Finance','Warehouse'}>} amount4),'$#,##0.0')

Now,I need to add 1 more filter in set analysis with if statement like below.I have added criteria as run this expression when we have document type ending with 'SA', 'AB', 'WY'.So I tried to add like below but its not working.

num(RangeSum(Sum({$<VERSION={0},ACCOUNT={'Sales','Finance','Warehouse'},vDocumentType=if(RIGHT($(vDocumentType), 2)='SA' or RIGHT($(vDocumentType), 2)='WA' or RIGHT($(vDocumentType), 2)='ZA',$(vDocumentType))
>} amount1),
Sum({$<VERSION={0},ACCOUNT={'Sales','Finance','Warehouse'},vDocumentType=if(RIGHT($(vDocumentType), 2)='SA' or RIGHT($(vDocumentType), 2)='WA' or RIGHT($(vDocumentType), 2)='ZA',$(vDocumentType))>} amount2),
Sum({$<VERSION={0},ACCOUNT={'Sales','Finance','Warehouse'},vDocumentType=if(RIGHT($(vDocumentType), 2)='SA' or RIGHT($(vDocumentType), 2)='WA' or RIGHT($(vDocumentType), 2)='ZA',$(vDocumentType))>} amount3))/
Sum({$<VERSION={0},ACCOUNT={'Sales','Finance','Warehouse'},vDocumentType=if(RIGHT($(vDocumentType), 2)='SA' or RIGHT($(vDocumentType), 2)='WA' or RIGHT($(vDocumentType), 2)='ZA',$(vDocumentType))>} amount4),'$#,##0.0')

How we can introduce if statement in set analysis ,could you please look into it and assist me.

Thanks in advance

[brunobertels · Accepted solution]

Hi 

May be like this 

IF( 

wildmatch( RIGHT($(vDocumentType), 2),'SA', 'AB', 'WY') , 

num(RangeSum(Sum({$<VERSION={0},ACCOUNT={'Sales','Finance','Warehouse'}
>} amount1),
Sum({$<VERSION={0},ACCOUNT={'Sales','Finance','Warehouse'}>} amount2),
Sum({$<VERSION={0},ACCOUNT={'Sales','Finance','Warehouse'}>} amount3))/
Sum({$<VERSION={0},ACCOUNT={'Sales','Finance','Warehouse'}>} amount4),'$#,##0.0')

, null() 

)

[Rsaiq]

Thanks @brunobertels ,its working fine...