0 Replies Latest reply: May 18, 2018 4:59 PM by Catalin Plitea RSS

    New Products sold by Customer - Set Analysis with P()/E() function

    Catalin Plitea

      Hello,

      I have the first 3 columns on the left loaded into Qlik Sense.

      I am trying to count New Products sold at each Customer in Period 2 (Period 2 is selected in the App) and not sold in Period 1 (by Customer)

      For easy visualising let's look at the modified table with the same data on the right: I want to have as a result all the coloured Products (yellow and red) from the Period2 Column (last column), that is  2 Products (4 and 5) for C1 and 3 Products(2,5,6) for C2.

       

      CustomerPeriodProduct...........................CustomerPeriod1Period2
      C1Period1Prod1C1Prod1Prod1
      C1Period1Prod2C1Prod2
      C1Period1Prod3C1Prod3Prod3
      C1Period2Prod1C1Prod4
      C1Period2Prod3C1Prod5
      C1Period2Prod4C2Prod1Prod1
      C1Period2Prod5C2Prod2
      C2Period1Prod1C2Prod3Prod3
      C2Period1Prod3C2Prod4
      C2Period1Prod4C2Prod5
      C2Period2Prod1C2Prod6
      C2Period2Prod2
      C2Period2Prod3
      C2Period2Prod5
      C2Period2Prod6

       

      I am using the next espression in a table with Customer as dimension:

      Count({<Product=E({<Period={'Period1'}>})>}Distinct Product) or

      Count({<Product-=P({<Period={'Period1'}>})>}Distinct Product)

        

      CustomerCount({<Product-=P({<Period={'Period1'}>})>}Distinct Product)Count({<Product=E({<Period={'Period1'}>})>}Distinct Product)
      C111
      C222

       

       

       

       

       

      The desired result would be 2+3, not 1+2.

      I am getting as a result only the Yellow Products, I suppose because Prod4 and Prod2 are possible in Period1, but for the other Customer.

      Even if I write the Expression with Aggr for Customer like this:

      Aggr(Count({<Product=E({<Period={'Period1'}>})>}Distinct Product), Customer),

      I am getting the same result.

      How can I make this expression not to disregard that I am interested in Products not Sold in Period 1 in the specific Customer in that row/or Aggr, not in any Customer in the app.

       

      Another question would be if '=E(...)' and '-=P(...)' has the same effect ?

       

      I have attached sample app and data.

       

      PS I can get the desired result Counting distinct products from both Periods - Count distinct Products in Period1, but I would like to know why my Expression with E()/P() function is not working and how I can make it work (if possible).

       

      Thank you!

      Catalin