Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
dena_reavis
Employee
Employee

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:

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

5 Replies
Not applicable

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
Employee
Employee
Author

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.

dena_reavis
Employee
Employee
Author

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
Employee
Employee
Author

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.

Not applicable

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