Set Analysis

rnepla83 — Fri, 29 Jul 2011 10:49:49 GMT

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.

Set Analysis

danielrozental — Fri, 29 Jul 2011 15:13:34 GMT

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)

Set Analysis

rnepla83 — Mon, 01 Aug 2011 07:45:31 GMT

Thanks a lot Daniel.

topic Set Analysis in QlikView

Set Analysis

Set Analysis

Set Analysis