Count combination of dimension

[bbi_mba_76]

Hi,

in my application I have orders with products, I need to count the orders with single products or combinations of product.

For example I have OrderID O0001 with product P1 and P2, so I would count this order for the combination of P1 and P2 (P1+2), Order O0002 has only the product P2, so I count this order as P2, O0003 will be counted for P2+3+4...

In the attached file a sample, the expeted result is

combination     |  orders count

P1	1
P1+2	1
P1+3+4+6	1
P2	2
P2+3+4	1
P3	2
P3+4	1
P3+4+6	1
P3+6	1
P4	1
P4+5+7	1
P5	1
P5+7	1
P6	1
P7	1

[jvishnuram]

Hi BBI,

I was trying your requirement, it was quiet complex and one thing i want to suggest to you., see the attachment below in that table i have made a pivot in which the orderid which is in bolder is having more than one contribution of the products. can you please check if it is fine for you or not.

[bbi_mba_76]

Thanks, in your solution I can see which orders have more products, but I need to count every product combination. The question is: in how many orders I sell P1&P2? and P3&P4? and so on.

Maybe I have to create a cross table in the script, and then flags every order for the correct combination...

[jvishnuram · Accepted solution]

Hi BBI,

Check this attachment.

[bbi_mba_76]

Thanks! I removed the dimension OrderID and using a calculated dimension =aggr(Concat(DISTINCT Product,'+'), OrderID) I obtain the correct result.

[rbecher]

Interesting solution but doesn't give all possible combinations of bought products, let's say for orde O0007:

P1+P3

P1+P4

P1+P6

P3+P4

P3+P6

P4+P6

P1+P3+P4

P1+P3+P6

P1+P4+P6

P3+P4+P6

P1+P3+P4+P6

Any further idea? I guess therefor a complex script loop would be needed..

- Ralf