Qlik Community

Ask a Question

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: 
robertomedellin
Contributor III
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
maxgro
MVP
MVP

in a text box?

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

View solution in original post

4 Replies
maxgro
MVP
MVP

in a text box?

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

View solution in original post

qlikrajan
Creator III
Creator III

Try this

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

MK_QSL
MVP
MVP

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

robertomedellin
Contributor III
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.