Announcements
cancel
Showing results for
Did you mean:
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.

Labels (3)

• ### Distinct

1 Solution

Accepted Solutions
MVP

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))``````
4 Replies
Contributor III
Author

Here's an example of my data

 Time Customer Product Sales 1-1-2019 1 P1 50 1-4-2019 1 P1 -50 1-2-2019 2 P2 25 1-3-2019 3 P3 30 1-6-2018 1 P1 20 1-7-2018 2 P2 5 1-8-2018 2 P4 1000 1-8-2018 3 P3 10 1-2-2018 2 P3 -1 1-4-2018 2 P3 1

And here's the result I am looking for:

 # distinct products Customer 2019 2018 1 0 1 2 1 2 3 1 1

MVP

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))``````
Partner - Champion

maybe this:

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

Contributor III
Author

Yes, it worked! Thanks!

Community Browser