# New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
Customer & Partners, DEC. 9, 11 AM ET: Qlik Product & Strategy Roadmap Session: Data Analytics REGISTER NOW
cancel
Showing results for
Did you mean:  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  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])

2 Replies  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])  Contributor III
Author

Brilliant - Much appreciated Tags
Community Browser