topic Showing customers who bought one product but not another in QlikView

Showing customers who bought one product but not another

sspe — Fri, 03 Oct 2014 10:17:26 GMT

Hi,

I'm struggling to find out how to show e.g. a list of customer who bougth one or more products but not another one. I thought it would be fairly straight forward and something I could do with e.g. Alternate state but I can't get my head around how to do it.

Basically I just want to have two listboxes with my products. If the user then e.g. want to see all the customers who have bought Product A but NOT Product B, then he selects Product A in the first one and Product B in the second one.

I'd expect this to be something a lot of people have done before but eventhough I've tried to google it, I haven't been able to find a good example of how it can be done.

Hopefully some of you can come up with some good examples?

Regards

Steen

Re: Showing customers who bought one product but not another

Colin-Albert — Fri, 03 Oct 2014 10:20:27 GMT

Some examples here

http://community.qlik.com/thread/112514

Re: Showing customers who bought one product but not another

Fri, 03 Oct 2014 11:09:13 GMT

In order to solve this with S.A. you need to have an aditional field in your data model (Products_Island) this new field should contain a distinct list of all your products but it must not be related to your data model, in other words, you will be able to display 2 listboxes, one will present Product values and the other Product_Island values, allowing you to have different selections.

Once this is done, you should use P() and E() functions in your expresssion:

Example:

Straight Table Chart:

Dim = Customers

Expression:

SUM({<Customers=P({<Product=Product>}Customers)>} Sales)

SUM({<Customers=E({<Product=Product_Island>}Customers)>} Sales)

Re: Re: Showing customers who bought one product but not another

sspe — Fri, 03 Oct 2014 11:27:51 GMT

Hi Colin,

The examples looks like something in the right direction, but it doesn't seems to really do what I want.

I have attached an example file (actually it's the example from your link I have "stolen"..) and what I want it that is I e.g. select SubProdID 101 in the SubProdID State1 box and then 103 in the next list box, then I want it so show SalesID 4 because this is the one that has bought SubProdID 101 but NOT 103.

The example looks very simple but I can't figure out how to modify it so it shows me the SalesID...:-(

Re: Showing customers who bought one product but not another

sspe — Wed, 08 Oct 2014 14:10:43 GMT

Hi Luis,

It took me a little while to get my syntax right, but your suggestion helped me in the right direction.

Thanks for you suggestion.