Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

cata_pli
New Contributor II

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

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