6 Replies Latest reply: Jan 13, 2018 5:51 AM by Ruben Marin

# Flag Customer IDs based on count and year

The question will be little tricky:

Define a field based on a ID, when we select the current month from Sales_Date (ex: Nov 2017):

- New IDs of current month = NewITM,

- Duplicate IDs from historic data define by year

>1 year OldITM

>2 year OldITM

>3 year OldITM

• ###### Re: Flag Customer IDs based on count and year

May be?

If(Count(CustID)>1, 'Duplicate', 'Regular')

• ###### Re: Flag Customer IDs based on count and year

loveisfail then how to flag the IDs by year?

• ###### Re: Flag Customer IDs based on count and year

Like this, May be?

If(Count(Year(Sales_Date))>1, 'NewITM', 'OldITM')

OR

If(Count(Year(Sales_Date))>0 and Count(Year(Sales_Date))<=1, '>1 year OLDITM',

If(Count(Year(Sales_Date))>1 and Count(Year(Sales_Date))<=2, '>2 year OLDITM'))

• ###### Re: Flag Customer IDs based on count and year

Hi Karan, you can add a field with the first sale for each Id:

Join (SalesTable)

CustID

Min(Sales_Date) as FirstSale

Resident SalesTable

Group By CustID;

Having the first sale and the current sale of each Id in the same records makes easier to check the difference in years:

Year(ThisSales)-Year(FirstSale) as YearsDiff

• ###### Re: Flag Customer IDs based on count and year

Thanks Ruben, but how to flag by year?

1 Year
2-3 Year
4-5 Year

• ###### Re: Flag Customer IDs based on count and year

It's like the YearsDiff field, ie:

If(Year(ThisSales)-Year(FirstSale) >=4, '4+ Year',

If(Year(ThisSales)-Year(FirstSale) >=2, '2-3 Year', '1 Year'

This only uses the year, not real dates differences, this is: between dec-17 and jan-18 there is 1 year; between jan-17 and dec-17 there are 0 years.