Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

alsellaro
New Contributor III

Set expression, aggregation, and intersections

Hi

I have a data table which has three fields: Client, Problem_ID, Ticket, and Status. I need a set expression to count the number of records that match the all of the following criteria:

  • Distinct client names
  • Problem_ID = 123 and Status <> W
  • Problem_ID = 456 and Status <> W

I tried the following, but it is obviously wrong:

count({$ <problem_id = 123, status <> 'W'> + <problem_id = 456, status <> 'W'>} DISTINCT client)

If I try using the intersection operator instead, the result is zero.

With the example data below, the expected counting should be 2 (as only Client A and Client D match the criteria).

clientproblem_idticketstatus
Client A123123456A
Client A456789012B
Client B123131517A
Client C456242628A
Client D123654321A
Client D456876543B

Thanks

Tags (1)
1 Solution

Accepted Solutions
MVP
MVP

Re: Set expression, aggregation, and intersections

Try this

=Count(DISTINCT {<client = P({<problem_id = {123}, status -= {'W'}>})*P({<problem_id = {456}, status -= {'W'}>})>} client)


Capture.PNG

7 Replies
MVP
MVP

Re: Set expression, aggregation, and intersections

Try this

=Count(DISTINCT {<client = P({<problem_id = {123}, status -= {'W'}>})*P({<problem_id = {456}, status -= {'W'}>})>} client)


Capture.PNG

alsellaro
New Contributor III

Re: Set expression, aggregation, and intersections

Thanks a lot. It does the trick

Other than the page below, do you know any other resource that describes the P() and E() element functions? I don't quite understand them yet.

MVP
MVP

Re: Set expression, aggregation, and intersections

Page below? Did you mean to share a link?

alsellaro
New Contributor III

Re: Set expression, aggregation, and intersections

MVP
MVP

Re: Set expression, aggregation, and intersections

This seems like a good example

Qlikview: Qlikview Indirect Set Analysis

MVP
MVP

Re: Set expression, aggregation, and intersections

alsellaro
New Contributor III

Re: Set expression, aggregation, and intersections

Thanks. Much appreciated.