Hi there,
I need to find out which of my customers has not bought which of the top 3 products based on sales within his industry.
I've got the following two tables. Table A is showing sales of the top 3 products we've sold to customers in a specific industry. Table B shows sales of a customer and the industry he belongs to.
Table A
| IndustryCode | Product | Sales |
|---|
| 0101 | AAA | 1100 |
| 0101 | ABC | 1200 |
| 0101 | BBC | 1400 |
| 0102 | AAA | 1300 |
| 0102 | DEF | 1500 |
| 0102 | ERT | 1600 |
Table B
| IndustryCode | Product | Customer | Sales |
|---|
| 0101 | AAA | Customer1 | 12 |
| 0101 | ADD | Customer1 | 14 |
| 0101 | XYZ | Customer1 | 15 |
| 0101 | AAA | Customer2 | 13 |
| 0101 | BBC | Customer2 | 17 |
| 0102 | AAA | Customer3 | 18 |
| 0102 | DEF | Customer3 | 11 |
| 0102 | ERT | Customer3 | 9 |
Now I would like to select a customer ... let's say Customer1. The result should be a table with all the top 3 products (without sales per industry) for the customers industry including the customers current sales. There should be a line with 0 as en entry for the customers current sales for each of the top 3 products of his industry the customer hasn't bought yet. Sales of products which are not in table A (the one with the top 3 products), should not appear in the results. The result should look like this:
| Product | Sales |
|---|
| AAA | 12 |
| ABC | 0 |
| BBC | 0 |
To make it a little more difficult: How about using a parameter to change the top 3 to top 2, even if i still have the top 3 products of each industry in table A. So there is no change in the underlying data, but the result should look like this:
I've tried some set analysis with E() and P() functions in it, but couldn't make it work. Is this approach the correct one or will I have to do a lot of scripting in the data editor?
Thanks and kind regards,
Christian
