topic Re: Count if a user has not one but two possible values in Qlik Automate

Count if a user has not one but two possible values

alexdelagarza — Wed, 07 Aug 2024 17:12:04 GMT

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!

Re: Count if a user has not one but two possible values

Chernov — Wed, 07 Aug 2024 17:12:50 GMT

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

Intersection operator = *

https://help.qlik.com/en-US/cloud-services/Subsystems/Hub/Content/Sense_Hub/ChartFunctions/SetAnalysis/set-operators.htm

Re: Count if a user has not one but two possible values

MatheusC — Wed, 07 Aug 2024 18:08:57 GMT

@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

Re: Count if a user has not one but two possible values

alexdelagarza — Wed, 07 Aug 2024 18:37:27 GMT

Hello Chernov,

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

Best

Re: Count if a user has not one but two possible values

alexdelagarza — Wed, 07 Aug 2024 19:33:23 GMT

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!

Re: Count if a user has not one but two possible values

MatheusC — Thu, 08 Aug 2024 13:28:17 GMT

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

Re: Count if a user has not one but two possible values

alexdelagarza — Mon, 09 Sep 2024 20:27:23 GMT

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

Re: Count if a user has not one but two possible values

MatheusC — Tue, 10 Sep 2024 18:43:54 GMT

@alexdelagarza
see applying the filter within the set analysis

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

- Matheus

Re: Count if a user has not one but two possible values

alexdelagarza — Tue, 10 Sep 2024 20:41:24 GMT

Thanks a lot Matheus, it worked.