5 Replies Latest reply: Mar 9, 2016 6:44 AM by Paromita Bhattacharjee

# Cross sale pivot

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

• ###### Re: Cross sale pivot

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 ?

• ###### Re: Cross sale pivot

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.

• ###### Re: Cross sale pivot

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

• ###### Re: Cross sale pivot

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.

• ###### Re: Cross sale pivot

Hi

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

Customer, Product
ABC, 1
A, 1
DEF, 2
A, 3
DEF, 4
ABC, 2
JKL, 3
]
;

outer join

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