Flag Customer IDs based on count and year

[karan_kn]

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

[Anil_Babu_Samineni]

May be?

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

[rubenmarin]

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

Join (SalesTable)

LOAD

     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

[karan_kn]

loveisfail then how to flag the IDs by year?

[karan_kn]

Thanks Ruben, but how to flag by year?

1 Year
2-3 Year
4-5 Year

[Anil_Babu_Samineni]

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'))

[rubenmarin]

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.