# 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
Search instead 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