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

[alexdelagarza]

Hi Matheus,

Following up on my question "Count if a user has not one but two possible values" that I posted on August 7th, you made the following suggestion:

"
@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"

Based on the above, I made the following expression:

=count( {$<ID_CARD= P({1<Escenario2={'204'}>})*P({1<Escenario2={'208'}>})>} ID_CARD )

I removed the "1" because it gave me a different number than expected.

So the expression was as follows:

=count( {$<ID_CARD= P({<Escenario2={'208'}>})*P({<Escenario2={'204'}>})>} ID_CARD )

My question is why in either of the two expressions does it return data from other scenarios?
In the following table you can see that in addition to scenarios 204 and 208, it shows me scenarios 101, 105, 108, etc.

How can I make it only show the records from scenarios 204 and 208 without having to select them in the filters?

Thank you in advance

[MatheusC · Accepted solution]

@alexdelagarza 
see applying the filter within the set analysis

count( {$<ID_CARD= P({<Escenario2={'208'}>})*P({<Escenario2={'204'}>}),Escenario2={'204','208'}>} ID_CARD )

- Matheus

[alexdelagarza]

Thanks a lot Matheus, it worked.