Skip to main content
Announcements
Accelerate Your Success: Fuel your data and AI journey with the right services, delivered by our experts. Learn More
cancel
Showing results for 
Search instead for 
Did you mean: 
JustinM
Contributor III
Contributor III

How to use if and Aggr together in expression

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

1 Solution

Accepted Solutions
GaryGiles
Specialist
Specialist

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])

 

View solution in original post

2 Replies
GaryGiles
Specialist
Specialist

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
Contributor III
Contributor III
Author

Brilliant - Much appreciated