A classical Basket analysis...
First: How do you define a "basket"? Is it if a customer has used the two products within a Quarter and in the same Country, or is it worldwide since the beginning of time?
Once you have defined your "basket", you should define an ID for that in the script. Further, you need to create a side table using
Load BasketID, Product as ProductB, ItemID as ItemB_ID Resident Baskets;
Then you will get a data model like
Now you can create your pivot table using Product and ProductB as dimensions, and
It might be possible to do all this in the pivot table using Set Analysis, Aggr and value loops, but it won't be easy...
Thanks for the reply.
The "Basket" that I have to create is the count(customers+country) which use any pair of products and it is for every quarter.
So what I am thinking is to create a table which will give me the Country and Customers which use atleast two products and the combination of those every 2 products.
Am I on the right track?
A little help would be highly appreciated!
It sounds to me as if your BasketID should be Quarter + Customer + Country:
Quarter & '|' & Customer & '|' & Country as BasketID
If you use the solution sketched above, but with this BasketID, I think it will work the way you want it. But you will need to have a selection of a single quarter, otherwise the expression will count the same customer several times - once per quarter.