Set Analysis

[rnepla83]

Hi to all!!

I would like to use the "Set Analysis" operator with Bookmarks.

I have two bookmarks called Set1 and Set2.

In these bookmarks there are the results of two different selections, for example:

     Set1 = Selection of product 'A'

     Set2 = Selection of product 'B'

These two results sets are supposed to have some common records (customers who boughtboth 'A' and 'B').

I loaded the data of who bought the product (clients) and obvious the product.

These are the results I expect to get from each operator:

- Set1 + Set2 (union)

All customers who bought 'A' and all customers who bought 'B'. Duplicated records should be excluded.

- Set1 - Set2 (difference)

All customers who bought 'A' and NOT have bought 'B'

- Set1 * Set2 (intersection)

Customers who bought both 'A' and 'B'

- Set1 / Set2 (XOR)

Customers who bought 'A' XOR 'B' (customers who bought both the models are excluded).

I tried to use these formulas for the union (The same formula was used for the other operators *, -, xor):

1) =count({<CLIENT=p({Set1}) + p({Set2})>}  DISTINCT CLIENT)

2) =count({Set1 + Set2}  DISTINCT CLIENT)

but in both cases the results aren't correct.

FOR EXAMPLE (look the image): If in the Set1 there are the products: 1,2,3,4,5,6 and in the Set2 there are the products 2,6,7,8, i would like to know all the clients have bought both products (if i analyze the intersection case). The intersection of Set1 and Set2 is {2}, but {2} is the intersection of all the bookmark. I want to find the intersection in one field of these sets (the client)... for example in dot notation would be Set1.Client * Set2.Client.

Which is the correct syntax for these operations for find the union, intersection, XOR and difference between the sets (Set1, Set2) on a specific field like customer?

Thanks a lot, Paolo.

[danielrozental · Accepted solution]

Paolo, you can try building a QVW to test all this and make it easier for people to help you.

To get customers who bought products in one set and in another set you can try something like this

count({<CLIENT={"=sum({SET_A} Sales)>0 AND sum({SET_B} Sales)>0 "}>} CLIENT)

A similar way

count({<CLIENT={"=sum({SET_A} Sales)>0"}>}*{"=sum({SET_B} Sales)>0 "}>} CLIENT)

[rnepla83]

Thanks a lot Daniel.