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

    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:

      CustomerProduct
      ABC1
      A1
      DEF2
      A3
      DEF4
      ABC2
      JKL3

       

      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:

      Product1234
      12
      212
      3102
      40101

       

      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:

      Product1234
      12
      22
      32
      41

       

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

       

      Thanks very much!!

        • Re: Cross sale pivot
          Srikanth P

          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
              Dena Reavis

              Capture2.PNGCapture.PNG

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

                    • Re: Cross sale pivot
                      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.

                • Re: Cross sale pivot
                  Paromita Bhattacharjee

                  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