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
