Hi, I am trying to find for the year 2019 sales, if a customer has made a purchase in previous rolling 5 years by creating a flag variable (1 = No purchase in past 5 yrs 0 = Purchase in past 5 yrs). Year 2019 Cust A, time gap between last 2 transactions is more than 5 years , so flag is 1 in 2019. Cust B time gap between last 2 transactions is less than 5 years, so flag is zero Cust C has made first and only transaction in 2019 so flag is 1 I have tried by using lag however it doesn't consider rolling five years Input CUSTOMER_ID Sale_Date Flag Year A 02/03/2010 . 2010 A 03/05/2013 . 2013 A 01/11/2019 1 2019 B 06/08/2017 . 2017 B 04/08/2019 . 2019 C 05/09/2019 1 2019 SALE_DT1= INTNX('MONTH',Sale_Date,0,"BEGINNING"); MONTHS_SINCE = INTCK('MONTH', LAG(SALE_DT1),SALE_DT1); IF CUSTOMER_ID ^= LAG(CUSTOMER_ID) THEN MONTHS_SINCE = .; IF MISSING(MONTHS_SINCE) THEN FLAG = 1; ELSE IF MONTHS_SINCE > 60 THEN FLAG =1; ELSE FLAG =0;
... View more