Advanced Product Comparisons

Tue, 22 Oct 2013 18:28:04 GMT

Hey All,

I have a question about some advanced product correlations I'm trying to work out. The end result I want is to see a list of product crossed with products to show how well correlated sales for one product are to another product.

Here is an example to help show what I want to see:

Table 1: List of Invoices (Raw Data):

Customer	Invoice	Product	Quantity
A	R01	P-1	1
B	R02	P-1	3
A	R03	P-3	4
C	R04	P-4	1
B	R05	P-2	3
A	R06	P-1	2
D	R07	P-3	10
C	R08	P-2	3
A	R09	P-2	1
B	R10	P-4	2

Table 2: Summary table to help show final calculations:

Customer	P-1	P-2	P-3	P-4	Totals
A	3	1	4	0	8
B	3	3	0	2	8
C	0	3	0	1	4
D	0	0	10	0	10
Totals	6	7	14	3	30

Table 3: The final data I want to see.

Product	Related	Correlation
P-1	P-2	100.0%
P-1	P-3	50.0%
P-1	P-4	100.0%
P-2	P-1	66.7%
P-2	P-3	33.3%
P-2	P-4	66.7%
P-3	P-1	50.0%
P-3	P-2	50.0%
P-3	P-4	0.0%
P-4	P-1	50.0%
P-4	P-2	100.0%
P-4	P-3	0.0%

As we can see every customer who purchased P-1 also purchased P-2 (customers A and B) which gives us 100% correlation. On the flip side, only 2/3 customers who purchased P-2 also purchased P-1 (customers A and B bought both, customer C only bought P-2) giving us a correlation of 66%.

Given the raw data in table 1, how could I show table 3 in a QlikView document. I've attached a document with Tables 1 and 2 already generated to help.

topic Advanced Product Comparisons in QlikView

Advanced Product Comparisons