How to use if and Aggr together in expression

[JustinM]

Hi

I have the following expression:

=sum([Amount Paid])-sum([VAT on Payment])-sum(if([VAT on Recovery]<>0,Recovery))+sum(if([VAT on Recovery]<>0,[VAT on Recovery]))

which works fine.

I am now trying to exclude certain items (i.e. Product named LB) from the calculation but I'm doing something wrong. My attempt was something like this.

=sum(Aggr(if(Product <> 'LB',(sum([Amount Paid])-sum([VAT on Payment])-sum(if([VAT on Recovery]<>0,Recovery))+sum(if([VAT on Recovery]<>0,[VAT on Recovery])))),Product))

Any assistance on how the expression should be structured will be much appreciated

[GaryGiles · Accepted solution]

It seems to me that you could get the results you are looking by using set analysis instead of an if statement.  For the first expression try:

=sum([Amount Paid])-sum([VAT on Payment])-sum({$<[VAT on Recovery]-={0}>} Recovery)+sum({$<[VAT on Recovery]-={0}>} [VAT on Recovery])

And the second expression try:

=sum({$<[Product]-={'LB'}>} [Amount Paid])-sum({$<[Product]-={'LB'}>} [VAT on Payment])-sum({$<[Product]-={'LB'},[VAT on Recovery]-={0}>} Recovery)+sum({$<[Product]-={'LB'},[VAT on Recovery]-={0}>} [VAT on Recovery])

[JustinM]

Brilliant - Much appreciated