I think I understand - you want customers that purchased this year for the first time OR customers that purchased this year AND had not purchased for the previous 5
have you tried this
if YEAR(min([First Date])) = 2018 then 'New'
elseif min([JD LOD date diff])>=5 and max(YEAR([Lastdate]))=2018 then 'New'
where the LOD is this
FIXED [Cuno]:Min(datediff('year',[First Date],today()) ) }
now in the data sample I could not find any records that met the second criteria so it returned null
see the attached
If this posts assists in resolving the question, please mark it helpful or as the 'correct answer' if it resolves the question. This will help other users find the same answer/resolution. Thank you.
matrix (2)_v10.5.twbx 100.1 KB
OK so maybe I wasn't explaining it.
A "New" Customer is either a customer who's first transaction was this year, or they haven't transacted with us in 5 years and now they're transacting again. I tried the above calculation but it is including those with transaction this year and last year as new--which isn't what I'm trying to get.
The "lastdate" is the date that we need to focus on because the firstdate for those is to eliminate duplicates on rental and lease transactions.
Now if there was a way I could put first, second and third dates in a column rather than a row with null values, i could do a date diff. hmmm
I cannot open your workbook as we still use 10.3 in the office, but I came up with something you could look to use:
My example (twbx attached) uses Superstore Data. I've created a few LOD expressions that you could modify to fit your needs.
Year Reference is just the max year in the data, so feel free to modify this to YEAR(TODAY()) or the like.
First step in flagging a new customer is straightforward: identify customers whose first order was place this year. So, we need to find their first order date (year, in this case):
Second step is slightly more complicated: find customers with an order this year, so find last order date year:
But we only want to flag them if their last order year before this year was N years ago. So we find their last order before this year:
So to put this all together:
Line 1 is truly a new customer: first order was placed this year. But second part flags customers who had an order this year and last order before this year was 2 or more years ago (so you could modify this to be 5 instead, but not useful to do with this dataset).
I added a "NewCustomerCD" to sort the "new" customer type to present it more clearly. True new customers appear first here and then 'returning' new customers appear second.
Hopefully this helps!
NewCustomerFLG_10.3.twbx 1.2 MB
YOU ARE AMAZING! Thank you so so much!!!