Set expression, aggregation, and intersections

[Anonymous]

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).

client	problem_id	ticket	status
Client A	123	123456	A
Client A	456	789012	B
Client B	123	131517	A
Client C	456	242628	A
Client D	123	654321	A
Client D	456	876543	B

Thanks

[sunny_talwar · Accepted solution]

Try this

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

[Anonymous]

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.

[sunny_talwar]

Page below? Did you mean to share a link?

[Anonymous]

Yes, sorry: https://help.qlik.com/en-US/sense/September2017/Subsystems/Hub/Content/ChartFunctions/SetAnalysis/se...

[sunny_talwar]

This seems like a good example

Qlikview: Qlikview Indirect Set Analysis

[sunny_talwar]

Another one

https://www.analyticsvidhya.com/blog/2014/01/set-analysis-qlikview/

[Anonymous]

Thanks. Much appreciated.