Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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 |
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.
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...
Hi BBI,
Check this attachment.
Thanks! I removed the dimension OrderID and using a calculated dimension =aggr(Concat(DISTINCT Product,'+'), OrderID) I obtain the correct result.
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