New Customers where Sales >2000

[micheledenardi]

Dear all,

i wrote a formula to find customers we sold during 2025 but now during last 3 years, the expression is this:

Count(DISTINCT {<SOLD_TO = p({<SALES_YEAR = {$(=Year(today(1)))}>})*e({<SALES_YEAR = {$(=Year(AddYears(today(1),-1))),$(=Year(AddYears(today(1),-2))),$(=Year(AddYears(today(1),-3)))}>})>} SOLD_TO )

 This expression works well, it extract the new customers, regardless the sales amount.

Now i need to enhance it by setting a sales amount limit, i need to consider a new customer only if the sum(sales) of current year is over 2000$ and if we didn't sold more than 2000$ during last 3 years.

How to do this?

Thanks

[Chanty4u]

Try this 

Count(DISTINCT {<  

    SOLD_TO = p({<  

        SALES_YEAR = {$(=Year(Today(1)))},  

        SALES = {">2000"}  

    >})  

    *  

    e({<  

        SALES_YEAR = {$(=Year(AddYears(Today(1),-1))),  

                      $(=Year(AddYears(Today(1),-2))),  

                      $(=Year(AddYears(Today(1),-3)))},  

        SALES = {">2000"}  

    >})  

>} SOLD_TO)

[micheledenardi · Accepted solution]

I create this expression which consider the amout of each year, not the total sales of all previus 3 years together:

Count(DISTINCT {

<SOLD_TO={"=sum({<SALES_YEAR = {$(=Year(today(1)))}>}SALES)>=2000"}>

*

  <SOLD_TO-={"=sum({<SALES_YEAR = {$(=Year(AddYears(today(1),-1)))}>}SALES)>=2000"}>

   *

    <SOLD_TO-={"=Sum({<SALES_YEAR = {$(=Year(AddYears(today(1),-2)))}>}SALES)>=2000"}>

   *

<SOLD_TO-={"=sum({<SALES_YEAR = {$(=Year(AddYears(today(1),-3)))}>}SALES)>=2000"}>}  SOLD_TO)