1 Reply Latest reply: Sep 6, 2016 3:52 AM by Gysbert Wassenaar

# Current customers sales of top 3 products within industry (Sense)

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

IndustryCodeProductSales
0101AAA1100
0101ABC1200
0101BBC1400
0102AAA1300
0102DEF1500
0102ERT1600

Table B

IndustryCodeProductCustomerSales
0101AAACustomer112
0101XYZCustomer115
0101AAACustomer213
0101BBCCustomer217
0102AAACustomer318
0102DEFCustomer311
0102ERTCustomer39

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:

ProductSales
AAA12
ABC0
BBC0

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:

ProductSales
AAA12
ABC0

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

• ###### Re: Current customers sales of top 3 products within industry (Sense)

See attached example