topic Re: Flag Customer IDs based on count and year in QlikView

Flag Customer IDs based on count and year

karan_kn — Wed, 25 Nov 2020 16:16:04 GMT

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 — Fri, 12 Jan 2018 15:13:26 GMT

May be?

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

rubenmarin — Fri, 12 Jan 2018 15:52:40 GMT

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 — Fri, 12 Jan 2018 17:13:04 GMT

loveisfail then how to flag the IDs by year?

karan_kn — Fri, 12 Jan 2018 17:15:46 GMT

Thanks Ruben, but how to flag by year?

1 Year
2-3 Year
4-5 Year

Anil_Babu_Samineni — Sat, 13 Jan 2018 01:21:58 GMT

Like this, May be?

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

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 — Sat, 13 Jan 2018 10:51:18 GMT

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.