topic Re: Set Analysis - Intersection exclusion. in QlikView

Set Analysis - Intersection exclusion.

Tue, 28 Jan 2014 18:12:16 GMT

Hi, everyone. I'm quite new to QV. I have the following question:

I have accounts, and sub-accounts. Various sub-accounts per account:

Account - Subaccount - Money

2601 - 001 - $500

2601 - 002 - $400

2602 - 001 - $900

2602 - 002 - $1000

2602 - 003 - $300

How can I say, via set analysis "I want the sum of money from every account and sub-account EXCEPT from account = {2601} & subaccount = {001}, and account = {2602} & subaccounts = {001,003} ? Meaning I would be excluding $500 from account 2601, and $900 and $300 from account 2602.

Thanks in advance!

Martín.

Re: Set Analysis - Intersection exclusion.

Nicole-Smith — Tue, 28 Jan 2014 18:28:25 GMT

The following two expressions both worked for me:

=sum({$<Subaccount=E({1<Account={2601},Subaccount={'001'}>}Subaccount)>*<Subaccount=E({1<Account={2602},Subaccount={'001','003'}>}Subaccount)>} Money)

and

=sum({$-(<Account={2601},Subaccount={'001'}>/<Account={2602},Subaccount={'001','003'}>)}Money)

You can see them in action in the text boxes in the attached .qvw file.

Re: Set Analysis - Intersection exclusion.

Tue, 28 Jan 2014 18:41:01 GMT

sum({$-1(<Account={2601},Subaccount={'001'}>/<Account={2602},Subaccount={'001','003'}>)}Money)

Re: Set Analysis - Intersection exclusion.

Tue, 28 Jan 2014 21:10:10 GMT

You are a genius, Nicole Smith. That E() function there (which I did not know about) changed my life forever. Thank you very much!

Re: Set Analysis - Intersection exclusion.

Nicole-Smith — Tue, 28 Jan 2014 21:11:07 GMT

I'm glad it was helpful!

Re: Set Analysis - Intersection exclusion.

Tue, 28 Jan 2014 21:11:21 GMT

Thank you too, Asim! I had to remove '-1' from '$-1', but aside from that (and it might be my fault) your answer helped me too. Thanks again!