Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Qlik Open Lakehouse is Now Generally Available! Discover the key highlights and partner resources here.
cancel
Showing results for 
Search instead for 
Did you mean: 
karan_kn
Creator II
Creator II

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


imglo.PNG

6 Replies
Anil_Babu_Samineni

May be?

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

Best Anil, When applicable please mark the correct/appropriate replies as "solution" (you can mark up to 3 "solutions". Please LIKE threads if the provided solution is helpful
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
Creator II
Creator II
Author

loveisfail then how to flag the IDs by year?

karan_kn
Creator II
Creator II
Author

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

Best Anil, When applicable please mark the correct/appropriate replies as "solution" (you can mark up to 3 "solutions". Please LIKE threads if the provided solution is helpful
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.