Qlikview set analysis

[Saranya1]

How we can count common records if we filter out two  values in a field?

For e.g i am calculating count(<{category={'a'}>number)

count(<{category={'b'}>number)

now if I want to have the count of  number that belongs to both a and b categories what will be the logic that involves some 'AND' condition or common count that belongs to both categories?

I Tried below 

count(<{category={'a'},{'b'}>number) but it works like OR logic  whereas I want only common records

[jwjackso · Accepted solution]

Using the data below:

Data:
Load * Inline [
dept,category,number
d1,a,100
d1,b,200
d2,b,200
d2,c,200
];

I added a third field, dept. 

Try using the P() function

=sum({<dept=P({<category={'a'}>}dept)*P({<category={'b'}>}dept)>}number)

[Oleg_Troyansky]

Hi,

You can use the function P(), as suggested before, or you can apply Boolean intersection to two Set Identifiers, each one with its own Set Modifier, like this:

count(<{category={'a'}> * <{category={'b'}> number) 

Also, I'd like to invite you to my lecture on Set Analysis and AGGR, that I will be delivering on-line on November 18th. You will learn many valuable techniques that can be used for advanced analytics, using AGGR and Set Analysis. Check out the link to the Masters Summit for Qlik in my signature below.

[Saranya1]

Hey Thanks a lot 🙂 This actually worked and it fixed my issue 🙂

[Saranya1]

I tried the intersection logic but it did not  work in my case since I have multiple filters I guess...

And yes function P() worked !