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...
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.
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.
