topic cross sales in QlikView

cross sales

Wed, 06 Apr 2011 12:51:21 GMT

Hello,

I am analyzing our customers purchasing patterns and I need to identify which are the products most commonly sold in conjunction with a given product. In concrete terms, I need to build a table like this:

Column 1: dimension 1 = product

Column 2: dimensions 2 = product AS cross-sold product

Column 3: expression = ratio: number of transactions with both product and cross-sold product / number of transactions with product

Column 4: expression = avg number of units of cross-sold product sold for each transaction where both product and cross-sold product are sold

...

I have a products table, a transactions table, and a transactions_content table in between that contains the product key, the transaction key, and the related information like number of units sold for each product... So basically what I need qlikview to do is, for each product, select the corresponding entries in the transactions_content and then in the transaction tables, and then "forget" about the product (but not the transactions) and come back to the products table. I hope it's clear...

I have tried a lot of potential solutions, but nothing works. I thought it would be as simple as aliasing the products field, but apparently it's not possible to alias fileds in QV... I'm a bit lost!

Of course, there's always the solution of loading the transactions_content and products tables twice and to retrieve the cross sales through the copy, but I find this both very inelegant and confusing as a user clicking the cross sold products is expecting the products to be selected...

Thanks for the help,

Andong

cross sales

IAMDV — Wed, 06 Apr 2011 13:00:51 GMT

Please can you post QV document with some sample data. I think you can alais the field names... however it would be really helpful , if you can take the effort to explain the issue with an example.

Cheers - DV

cross sales

Fri, 08 Apr 2011 09:32:46 GMT

Hi,

I'm still using the free version of QV, I still need to convince my boss to buy it. You won't be able to open it, right? Anyway, it's attached.

Actually, what I want to do is very simple, and you will get it just by looking at the diagram attached. There are the 3 tables involved, and I need to build a pivot table that will help me get which products are the most frequently found in the same transactions as each product. A kind of "the customers who bought this item also liked..." chart.

Thanks,

Andong

cross sales

Fri, 08 Apr 2011 17:01:49 GMT

Sorry my 2nd attachment erased the 1st one... the QV doc is attached.

Andong