Finding New and Repeating Customer

[saurav5246]

Hi All,

Currently I am facing issue while finding New and Repeating Customer and need suggestion.

Logic is like this:

01/01/2019	New :-first time Ordered Year
01/10/2019	New :-first time Ordered Year
01/11/2019	New :-first time Ordered Year
01/01/2020	Repeating: as same customer is repeating as it has Ordered in 2019
01/01/2021	Repeating: as same customer is repeating as it has Ordered in 2020
01/09/2022	Repeating: as same customer is repeating as it has Ordered in 2021
01/10/2024	New: as it has a gap.(did not Ordered in 2023)
01/10/2024	New: as it has a gap.(did not Ordered in 2023)
01/01/2025	Repeating: as same customer is repeating as it has Ordered in 2024
01/10/2025	Repeating: as same customer is repeating as it has Ordered in 2024
01/10/2027	New: as it has a gap.(did not Ordered in 2024)

I am using following logic but not getting actual output.

load Customer_code,
SO_Year,
if(RowNo()=1, 'New', previous(SO_Year)) as Previous_SO_Year,
SO_Year-previous(SO_Year) as differnce,
//if(RowNo()=1 , 'New', if(SO_Year= previous(SO_Year),'New','Returning' )) as RET;

if(RowNo()=1 , 'New', if(SO_Year= previous(SO_Year) or
(SO_Year-previous(SO_Year)=1),'Returning','New' )) as RET;
;

LOAD
"SO No",
Date#("SO Date(Date)",'DD-MMM-YYYY') as "SO Date(Date)",
date(Date#("SO Date(Date)",'DD-MMM-YYYY'),'YYYY') as SO_Year_1,
Year( Date#("SO Date(Date)",'DD-MMM-YYYY')) as SO_Year,

"Customer Code" as Customer_code,
"Customer Name"

I am attaching excel sheet for reference.

Thanks in advance.

[LRuCelver · Accepted solution]

Does this achieve what you wanted?

//	Load all Data
Data:
NoConcatenate Load
    "Customer Name",
    Customer_code,
    SO_Date
From [lib://AttachedFiles/test23.xlsx]
(ooxml, embedded labels, table is Sheet1);


//	"Sort" the rows by customer and date and number them; Add the Year for easier calculations
OrderedData:
NoConcatenate Load
	*,
    Year(SO_Date) as SO_Year,
    RowNo() as RowID
Resident Data
Order By Customer_code, SO_Date;

Drop Table Data;


//	Left join the year of the previous row for each customer -> the first row for every customer will have null as the previous year
Left Join(OrderedData) Load Distinct
	RowID + 1 as RowID,
    Customer_code,
    SO_Year as SO_PreviousYear
Resident OrderedData;


//	Load final table; Check whether the previous year is within 1 year from the current one.
Final:
NoConcatenate Load
    "Customer Name",
    Customer_code,
    SO_Date,
    SO_Year,
    If(SO_Year - SO_PreviousYear <= 1, 'Returning', 'New') as ReturningCustomer
Resident OrderedData;

Drop Table OrderedData;

[saurav5246]

Thanks @LRuCelver 

This is working fine and getting desired output.