Count if a user has not one but two possible values

[alexdelagarza]

Hello,

I have the following table:

USER	Scenario
IMDM26963748	A
IMDM24755867	A
IMDM27296460	A
IMDM22749898	A
IMDM23681226	A
IMDM23681226	B
IMDM28166404	A
IMDM28166404	B
IMDM25781532	A
IMDM25781532	B
IMDM28134074	A
IMDM28134074	B
IMDM26799093	A
IMDM26861096	A
IMDM24102807	A
IMDM27644821	A

I need to count only the users that has both scenarios A and B.

I've tried the following:

Try 1:

= Count( {<Escenario2 = {100},Escenario2 = {101}>}USER)

But it counts 16 instead of 4 which is the correct answer.

Try 2:

= Count( {<Scenario={'A','B'}>}USER)

Same result than Try 1.

Thanks!

[Chernov]

Count( {<Escenario2 = {100}>*<Escenario2 = {101}>}USER)

Intersection operator = *

https://help.qlik.com/en-US/cloud-services/Subsystems/Hub/Content/Sense_Hub/ChartFunctions/SetAnalys...

[MatheusC · Accepted solution]

@alexdelagarza 
Use with the P() element function, represent the set of possible values:

count( {$<USER= P({1<Scenario={'A'}>})*P({1<Scenario={'B'}>})>} distinct USER )


- Matheus

[alexdelagarza]

Hello Chernov,

Thanks for your reply, It still counting all the lines (16), I'm attaching the test file.

Best

[alexdelagarza]

Hi Matheus,

I tried your function and it gave me another result (504)

Then I tried your function omitting the number 1, as follows:

=count( {$<ID_CARD= P({<Escenario2={'100'}>})*P({<Escenario2={'101'}>})>} distinct ID_CARD )

And it work, this function gave me the expected result (24)

To be honest I just try omitting the number one without knowing what I was doing.

Now I can continue with other scenarios to check if it still works.

Thanks!

[MatheusC]

As a complement to explanation. The function P() returns the possible values, so it can be affected by the user's selection. The identifier 1 is used to ignore all user selections.

I am happy to help,
- Matheus