Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.

Turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Announcements

Join us at Qlik Connect for 3 magical days of learning, networking,and inspiration! __REGISTER TODAY__ and save!

- Qlik Community
- :
- Forums
- :
- Data Integration
- :
- Qlik
- :
- Qlik Application Automation
- :
- Re: Count if a user has not one but two possible v...

Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

alexdelagarza

Contributor III

2024-08-07
12:19 PM

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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!

2 Solutions

Accepted Solutions

MatheusC

Specialist II

2024-08-07
02:08 PM

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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

MatheusC

Specialist II

2024-09-10
02:43 PM

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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

8 Replies

Chernov

Creator

2024-08-07
01:12 PM

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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

Intersection operator = *

MatheusC

Specialist II

2024-08-07
02:08 PM

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

@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

2024-08-07
02:37 PM

Author

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Hello Chernov,

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

Best

402 Views

alexdelagarza

Contributor III

2024-08-07
03:33 PM

Author

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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!

386 Views

MatheusC

Specialist II

2024-08-08
09:28 AM

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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

343 Views

alexdelagarza

Contributor III

2024-09-09
04:27 PM

Author

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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

228 Views

MatheusC

Specialist II

2024-09-10
02:43 PM

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

@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

Contributor III

2024-09-10
04:41 PM

Author

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Thanks a lot Matheus, it worked.