Skip to main content
Announcements
Qlik Connect 2024! Seize endless possibilities! LEARN MORE
cancel
Showing results for 
Search instead for 
Did you mean: 
ali_hijazi
Partner - Master II
Partner - Master II

AND in set analysis

Hello
suppose I got a field F with the following values V1, V2, V3, V4, and V5
Suppose the user selects V1, and V2 then
I want to sum the sales where Client has both V1 and V2 rows
suppose Client C1 has rows with values V1, and V2 under F
and another  client has only V1 rows then this client should be excluded

if I use sum({<F={V1,V2}> sales) then C1, and C2 will appear in the chart wheras I want only C2 to be included in the calculation

kindly advise on how to accomplish this

I can walk on water when it freezes
Labels (1)
1 Solution

Accepted Solutions
ali_hijazi
Partner - Master II
Partner - Master II
Author

managed to create a dynamic set analysis with intersection logic
sum({< $(=Concat(distinct 'F={[' & F,']}>*<') & ']}')>} sales)
here if the user selects values V1, V2, and V3
the $(=Concat(distinct 'F={[' & F,']}>*<') & ']}') 
returns
<F={v1}>*<F={v2}>*<F={v3}>
and so on

I can walk on water when it freezes

View solution in original post

6 Replies
steeefan
Specialist
Specialist

Sounds to me like "Customers who bought product A and B". Have a look at this post (translated from German).

ali_hijazi
Partner - Master II
Partner - Master II
Author

to a certain extent yes
but in my case I may select more than 2 values
I know I need to use the intersection instead of union
but I need to make the set analysis syntax dynamic

I can walk on water when it freezes
ali_hijazi
Partner - Master II
Partner - Master II
Author

managed to create a dynamic set analysis with intersection logic
sum({< $(=Concat(distinct 'F={[' & F,']}>*<') & ']}')>} sales)
here if the user selects values V1, V2, and V3
the $(=Concat(distinct 'F={[' & F,']}>*<') & ']}') 
returns
<F={v1}>*<F={v2}>*<F={v3}>
and so on

I can walk on water when it freezes
Or
MVP
MVP

Out of curiosity, does that actually work? Intuitively, I would expect it to return an empty set, since no rows are at the intersect of F=v1 and F=v2 (each row has only one F value). I would have expected this to require the p() operator.

ali_hijazi
Partner - Master II
Partner - Master II
Author

Yes it works

Very similar to the example of customers who bought both products A and B

 

I can walk on water when it freezes
ali_hijazi
Partner - Master II
Partner - Master II
Author

The filter conditions with the intersection is built based on what the user selects which are in the P set (set of possible values)

I can walk on water when it freezes