Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

robertomedellin
New Contributor III

How to count distinct with an intersection within the same field?

Hello everyone!

I have a table

User 1 opens document A

User 1 opens documet B

User 2 opens document C

User 3 opens document B

User 4 opens B

User 4 opens C

User 5 opens A

User 5 opens B

User 5 opens C

User 6 opens A

I can do Count with set analysis how many opened any document.

=Count(DISTINCT   {<Document={'A'}>} User)    That's 3 in this example.

How can I do something like this: Count users that have opened BOTH A and B. 2 in this example (User 1 and User 5)

Because =Count(DISTINCT   {<Document={'A','B'}>} User) gives me users that have opened Either A or B. 5 in this example (1,5, + 3,4,6)

Any help will be much apreciated.

Thanks!

1 Solution

Accepted Solutions
Highlighted
MVP
MVP

Re: How to count distinct with an intersection within the same field?

in a text box?

=sum(if(aggr(count({$ <doc={B,A}>} distinct doc), user)=2,1))

View solution in original post

4 Replies
Highlighted
MVP
MVP

Re: How to count distinct with an intersection within the same field?

in a text box?

=sum(if(aggr(count({$ <doc={B,A}>} distinct doc), user)=2,1))

View solution in original post

Highlighted
qlikrajan
Contributor III

Re: How to count distinct with an intersection within the same field?

Try this

=Count({<Document={'A'}>} DISTINCT User)+ Count({<Document={'B'}>} DISTINCT User)-  Count({<Document={'A','B'}>} DISTINCT User)

Highlighted
MVP
MVP

Re: How to count distinct with an intersection within the same field?

=count({<User = p({<Document = {A}>}) * p({<Document = {B}>}) >} distinct User)

Highlighted
robertomedellin
New Contributor III

Re: How to count distinct with an intersection within the same field?

Now my dilemma is, How can I mark the 3 as correct answers?!!

Thanks Massimo, Thanks QlikRajan, Thanks Manish!

Everyone else, this 3 worked:

=sum(if(aggr(count({$ <Document={'B','A'}>} distinct Document), user)=2,1))

=Count({<Document={'A'}>} DISTINCT User)+ Count({<Document={'B'}>} DISTINCT User)-  Count({<Document={'A','B'}>} DISTINCT User)

=count({<User = p({<Document = {'A'}>}) * p({<Document = {'B'}>}) >} distinct User)

I guess the second one might be easier on CPU.

Thanks again guys.