Aggr to filter max sales previous years compared with year

[Riccardo_1]

Hello,

I try to find a solution to count unique customers for year 2018 where customer sales qty in 2018 is greater than 50 and did not have sales qty on year base above 50 in previous years.

The data looks like below.

Customer, InvYear, Qty, Country
A, 2016, 49, UK
A, 2017, 25, UK
A, 2017, 30, UK
A, 2018, 100, UK
B, 2016, 30, UK
B, 2017, 80, UK
B, 2017, -80, UK
B, 2018, 80, UK
C, 2016, 60, UK
C, 2018, 10, UK
D, 2016, 15, ES
D, 2016, 25, ES
D, 2016, 15, ES
D, 2017, 35, ES
D, 2018, 55, ES
E, 2014, 10, ES
E, 2015, 20, ES
E, 2016, 30, ES
E, 2018, 55, ES

Output expected
Country,2018 New Customers
------------------
UK, 1
ES, 1

For UK:
    Customer A doesn't count because has in 2017 qty>50
    Customer B counts because all qty previous year<50 and qty 2018>50
    Customer C doesn't count because 2018 < 50
For ES:
    Customer D doesn't count because qty 2016 > 50
    Customer E counts because all qty previoous years<50 and qty 2018>50

I have a straight table with one dimension (Country) and 1 calculated field with header "2018" with formula to count new customers for 2018.

I did try (But this returns an empty table)
Count({<Customer={"=Max(Aggr(sum({<InvYear={'<$(=Year(AddYears(Today(),-3)))'}>} Qty), Customer, InvYear))<=50"}
*{"=Sum({<InvYear={$(=Year(AddYears(Today(),-3)))}>} Qty)>50"}>} distinct Customer)

And I did try other way (But this one doesn't return correct output because Qty isn't based on year.)

Count( Aggr(IF(Sum({<InvYear={"<$(=Year(AddYears(Today(),-3)))"}, Qty={">50"}>} Qty)=0
AND Sum({<InvYear={"$(=Year(AddYears(Today(),-3)))"}, Qty={">50"}>} Qty)>0, Count(distinct Customer)), Country, Customer))

Does anyone have a suggestion on how to fix this. I don't see it.

[MayilVahanan · Accepted solution]

HI @Riccardo_1 

Your expression works with little bit quotes different.

Count({<Customer={'=Max(Aggr(sum({<InvYear={"<$(=Year(AddYears(Today(),-3)))"}>} Qty), Customer, InvYear))<=50'}
*{"=Sum({<InvYear={$(=Year(AddYears(Today(),-3)))}>} Qty)>50"}>} distinct Customer)

O/P:

[Riccardo_1 · Accepted solution]

Hello MayilVahanan,

Thank you for your answer !

I did make this change and see more records now, but results were not correct. With this new insight I did see I had to change the * between sets with – and change <=50 with >50.

Count({<Customer={"=Sum({<InvYear={$(=Year(AddYears(Today(),-3)))}>} Qty)>50"}-{'=Max(Aggr(sum({<InvYear={"<$(=Year(AddYears(Today(),-3)))"}>} Qty), Customer, InvYear))>50'}>} distinct Customer)

Results seems ok now.

Many thanks for pushing me in right direction.

Kind regards

[MayilVahanan]

Hi @Riccardo_1 

Happy to hear this. You're welcome