topic New Customers where Sales >2000 in App Development

New Customers where Sales >2000

micheledenardi — Tue, 04 Mar 2025 16:26:25 GMT

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

Re: New Customers where Sales >2000

Chanty4u — Tue, 04 Mar 2025 17:44:02 GMT

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)

Re: New Customers where Sales >2000

micheledenardi — Mon, 10 Mar 2025 12:01:11 GMT

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)