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

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

    Christian Herzog

      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
      0101ADDCustomer114
      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