AND in set analysis

[ali_hijazi]

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

[steeefan]

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

[ali_hijazi]

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

[ali_hijazi · Accepted solution]

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

[Or]

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]

Yes it works

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

[ali_hijazi]

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