Skip to main content
Announcements
Join us at Qlik Connect for 3 magical days of learning, networking,and inspiration! REGISTER TODAY and save!
cancel
Showing results for 
Search instead for 
Did you mean: 
alexdelagarza
Contributor III
Contributor III

Count if a user has not one but two possible values

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!

Labels (1)
2 Solutions

Accepted Solutions
MatheusC
Specialist II
Specialist II

@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

Did you find a solution to your question? Mark the solution as accepted and if you found it useful, press the like button!

View solution in original post

MatheusC
Specialist II
Specialist II

@alexdelagarza 
see applying the filter within the set analysis

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

- Matheus

Did you find a solution to your question? Mark the solution as accepted and if you found it useful, press the like button!

View solution in original post

8 Replies
Chernov
Creator
Creator

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

Intersection operator = *

 

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

MatheusC
Specialist II
Specialist II

@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

Did you find a solution to your question? Mark the solution as accepted and if you found it useful, press the like button!
alexdelagarza
Contributor III
Contributor III
Author

Hello Chernov,

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

Best

alexdelagarza
Contributor III
Contributor III
Author

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
Specialist II
Specialist II

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

Did you find a solution to your question? Mark the solution as accepted and if you found it useful, press the like button!
alexdelagarza
Contributor III
Contributor III
Author

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
Specialist II
Specialist II

@alexdelagarza 
see applying the filter within the set analysis

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

- Matheus

Did you find a solution to your question? Mark the solution as accepted and if you found it useful, press the like button!
alexdelagarza
Contributor III
Contributor III
Author

Thanks a lot Matheus, it worked.