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

[Anonymous]

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!

[maxgro · Accepted solution]

in a text box?

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

[Anonymous]

Try this

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

[MK_QSL]

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

[Anonymous]

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.