topic Re: Set expression, aggregation, and intersections in App Development

Set expression, aggregation, and intersections

Anonymous — Mon, 16 Apr 2018 13:18:29 GMT

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

Re: Set expression, aggregation, and intersections

sunny_talwar — Mon, 16 Apr 2018 16:47:40 GMT

Try this

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

Re: Set expression, aggregation, and intersections

Anonymous — Mon, 16 Apr 2018 16:53:09 GMT

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.

Re: Set expression, aggregation, and intersections

sunny_talwar — Mon, 16 Apr 2018 16:55:09 GMT

Page below? Did you mean to share a link?

Re: Set expression, aggregation, and intersections

Anonymous — Mon, 16 Apr 2018 16:56:11 GMT

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

Re: Set expression, aggregation, and intersections

sunny_talwar — Mon, 16 Apr 2018 17:16:36 GMT

This seems like a good example

Qlikview: Qlikview Indirect Set Analysis

Re: Set expression, aggregation, and intersections

sunny_talwar — Mon, 16 Apr 2018 17:17:03 GMT

Another one

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

Re: Set expression, aggregation, and intersections

Anonymous — Mon, 16 Apr 2018 17:32:08 GMT

Thanks. Much appreciated.