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.
