Count of New Customers within last 360 days

[Andrew_B]

Hi there

Im looking for help to dynamically count new customers who have purchased in the current year

A new customer would be defined as one that either had sales <=0 or no record of sales at all (null) within the last 360 days.  Im looking to accomplish this in Set analysis

Fields are

CustomerID  (customer identifier)

InvoiceDate (date of invoice - (text))

Linetotal (total line sales - (number))

vFisToday (variable = today)

vFisYear (variable = current year)

Appreciate the assistance

[JuanGerardo]

Hi @Andrew_B, I think you can use the E() function in a set analysis expression:

Count({<InvoiceDate={'$(=Today())'}, Linetotal={">0"}, CustomerID=E({<InvoiceDate={"<$(=Today())>=$(=Date(Today() - 360))"}>}, Linetotal={">0"})>} Distinct CustomerID)

This expression could be translated to: count distinct customers with invoice date of today and linetotal positive, excluding all that have purchased between today and 360 days ago.

JG

[Andrew_B · Accepted solution]

Thanks, JuanGerardo

The E() function made all of the difference

Below is the final result

Count({<PeriodYear={'$(vFisYear)'},  LineTotal={">0"},CustomerID=E ({1<InvoiceDate = {"<=$(=date(Num('$(vFisToday)')-360))"}>})>} Distinct CustomerID)

Appreciate your help