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.
