2 Replies Latest reply: Oct 28, 2013 12:48 PM by Paweł Sowa RSS

    How to get total sales for new products in comparison to previous year?

    Paweł Sowa

      Hi QlikView Masters

       

      I'm trying to calculate some economic effects in a function that would take data for only newly introduced products. For example:

       

      Year 2010, Products: A, B
      Year 2011, Products: A, B, C
      Year 2012, Products: A, B, C, D, E

       

      I need to have variables or a table that will contain a sum of sales only for the new product(s), so there should be:

       

      New_Product_Effect_2011: sum of sales of C

      New_Product_Effect_2012: sum of sales of D and E

       

      or a table instead of two variables here.


      In SQL I would write:

       

      SELECT SUM(Sale_Value) FROM SALES_TABLE

      WHERE Year = 2011 AND Product_id NOT IN (

          SELECT Product_id FROM SALES_TABLE

          WHERE Year = 2010

      )


      But I don't know how to achieve it in QV logic without using hardcoded products names. Also, is there a way to get something like this in QV:

       

      SELECT Product_Name, SUM(Sale_Value) FROM SALES_TABLE

      WHERE Year = 2011 AND Product_id NOT IN (

          SELECT Product_id FROM SALES_TABLE

          WHERE Year = 2010

      )

      GROUP BY Product_Name


      ?

       

      I'm attaching my sample files, but my current solution (separate variables for each year) it is not necessarily the best way for this purpose. I would be very happy to have at least the total sales value only for new products in a year or possibly the sales for each of new products in a year.

       

      Many thanks in advance for any help...

       

      Best regards,
      Paweł