Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
May be?
If(Count(CustID)>1, 'Duplicate', 'Regular')
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
loveisfail then how to flag the IDs by year?
Thanks Ruben, but how to flag by year?
1 Year
2-3 Year
4-5 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'))
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.