# QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
On May 18th at 10AM EDT we will answer your QlikView questions live. REGISTER
cancel
Showing results for
Search instead for
Did you mean:
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
MVP

in a text box?

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

4 Replies
MVP

in a text box?

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

Creator III

Try this

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

MVP

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

Contributor III
Author

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.