Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
jorgen_santberg
Contributor III
Contributor III

Distinct products per customer versus last year

Hi all,

 

I have a table with customers and I want to know the distinct number of products they are buying.

For the selected year I tried the formula

Count({<Product ={"=Sum(Sales) >0"}, Month=>}Distinct Product )

but also the products returned by customers where counted so now I am using the formula

Count(Distinct Aggr(If(Sum({$<Month=>}Sales)>0,Product ), Customer, Product ))

and now I am getting the correct number.

 

Now I want to get the number of distinct products per customer for the previous year. I tried

Count(Distinct Aggr(If(Sum({$<Year={$(=Max(Year)-1)},Month=>}Sales) >0, Product ), Customer, Product ))

but I am getting the number of distinct products per customer with sales in the selected year and the previous year.

 

Any advise would be appreciated!

Labels (3)
1 Solution

Accepted Solutions
sunny_talwar

Try this

Count({$<Year = {$(=Max(Year)-1)}, Month>} DISTINCT Aggr(
    If(Sum({$<Year = {$(=Max(Year)-1)}, Month>}Sales) > 0,
        Only({$<Year = {$(=Max(Year)-1)}, Month>} Product)
    )
, Customer, Product))

View solution in original post

4 Replies
jorgen_santberg
Contributor III
Contributor III
Author

Here's an example of my data

TimeCustomerProductSales
1-1-20191P150
1-4-20191P1-50
1-2-20192P225
1-3-20193P330
1-6-20181P120
1-7-20182P25
1-8-20182P41000
1-8-20183P310
1-2-20182P3-1
1-4-20182P31

 

And here's the result I am looking for:

 # distinct products
Customer20192018
101
212
311

 

sunny_talwar

Try this

Count({$<Year = {$(=Max(Year)-1)}, Month>} DISTINCT Aggr(
    If(Sum({$<Year = {$(=Max(Year)-1)}, Month>}Sales) > 0,
        Only({$<Year = {$(=Max(Year)-1)}, Month>} Product)
    )
, Customer, Product))
agigliotti
Partner - Champion
Partner - Champion

maybe this:

=Count( {< Product = {"=Sum( {< Year = {$(=Max(Year)-1)}, Month= >} Sales )>0"} >} Distinct Product )

jorgen_santberg
Contributor III
Contributor III
Author

Yes, it worked! Thanks!