Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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!
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))
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 |
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))
maybe this:
=Count( {< Product = {"=Sum( {< Year = {$(=Max(Year)-1)}, Month= >} Sales )>0"} >} Distinct Product )
Yes, it worked! Thanks!