Cross sale pivot

[dena_reavis]

Hi,

I am trying to build a pivot table with a product dimension used twice, so I can see a count of customers who bought the different combinations of products. I have no trouble getting the first calculation, where it is counting the customers who purchased product, 1, 2 3, or 4 - but not the combinations of 1 and 2, 1 and 3, 1 and 4, etc.

Data:

Customer	Product
ABC	1
A	1
DEF	2
A	3
DEF	4
ABC	2
JKL	3

I am looking for a count like this matrix-type chart below. I want to show that 2 different customers (ABC and A) bought product 1, but only one customer (ABC) bought both products 1 and 2. Only one customer, DEF, bought the combination of product 2 and 4.

What I want:

Product	1	2	3	4
1	2
2	1	2
3	1	0	2
4	0	1	0	1

In my pivot table, I have the Product as dimension one and two. I then dragged the second product in place to the column headers. The expression I used was =Count({$<[Product]={*}>} [Customer]). Not really working, I am basically getting this below: But it is the other intersections I need, in green text above. 

What I get:

Product	1	2	3	4
1	2
2		2
3			2
4				1

Could someone help me with the expression to calculate the other intersections, in green?

Thanks very much!!

[#2]

You may need to add the another field in the script to handle instead of same field using multiple times.

Can you please elaborate how to define the 2nd dimension & matrix output ?

[dena_reavis]

Dathu,

I would be happy to add additional fields if I need to. To answer your question, right now, the first and second dimensions of my pivot chart are Product. The matrix output I would like is above. The example intersection I have outlined in blue would be customers who bought the combination of products 1 and 3.  Only 1 customer (A above) bought product 3 and 1.

[dena_reavis]

Here is a little more elaboration.. This could be considered a market basket type of analysis. I have 10 products, I want to count the customers who bought products in the intersections of:

product 1 and 2

product 1 and 3

product 1 and 4

product 1 ..... 10

product 2 and 3

product 2 and 4

and so on, until I have all the possible combinations (because order does not matter - buying product 1 and 2 is the same as buying product 2 and 1).

I thought maybe a set analysis expression could do this. Please help!

[dena_reavis]

I would note that I have read several of the market basket responses but I find that my issue is different because I have data in just one field in one table. Someone had suggested duplicating the product column and counting where they are not equal, which I don't see how that will work. If I duplicate the column, the two fields will be equal. Other suggestions have separate tables of data or separate fields or some other reason I can't use the solution. Any help is appreciated. Thank you.

[#6]

Hi

      Just load the table twice and outer join on Customer. You will get the desired result. Use expression : Count(Distinct Customer)

LOAD * INLINE [
Customer, Product
ABC, 1
A, 1
DEF, 2
A, 3
DEF, 4
ABC, 2
JKL, 3
];

outer join

LOAD * INLINE [
Customer, Product1
ABC, 1
A, 1
DEF, 2
A, 3
DEF, 4
ABC, 2
JKL, 3
];

Product		1	2	3	4
1		2	1	1	-
2		1	2	-	1
3		1	-	2	-
4		-	1	-	1