Hi Team,
we have achieved lost customers data as per selected date range however we are facing issue with future transactions in lost customer data from selected date. Could any one help us to sort out the issue.
Eg:
We have data from 01-Jan-2020 to 14-Feb-2023 in table, selected date range is "01-Jan-2020 to 31-Dec-2022" in this case we have to restrict customers who have done transaction after max selected date and we need to show customers only who have not purchased in selected period and they have purchased before slected Min date.
We have tried
1.sales amount in selected period == blank then Lost customers ----- it is working but we are getting post selected date customers also.
2. (sales amount in selected period == blank ) && max(Txn date) <= max(dim date [date]) --but still we are getting same.
3. (sales amount in selected period == blank ) && (max(Txn date) <= max(dim date [date])) && (sales amount before selected period)>0 ----- we are facing blank.
we have three tables = customer table , Transaction table and dim_date table
Thank you for your help.
