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

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

Anonymous — Tue, 17 Mar 2015 20:09:58 GMT

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 — Tue, 17 Mar 2015 20:19:34 GMT

in a text box?

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

Anonymous — Tue, 17 Mar 2015 20:30:36 GMT

Try this

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

MK_QSL — Tue, 17 Mar 2015 20:37:35 GMT

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

Anonymous — Tue, 17 Mar 2015 22:20:20 GMT

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.