This is the logic i have
From date <last 12month and to date <today New ,
From Date >12month and todate > today,renew,
Todate <today , lost,
According to above logic
if fromdate is less than 12 months and todate is less than today date then considered as "New"
if fromdate is greater than 12 months and todate is greater than today date then "renew"
if todate is less than today date then lost
check this below data
| CAR/131094 | 0 | 19-09-2019 | 15-05-2020 | 2015 | lost |
| P/07/MTS/2019/019 | - | 6/1/2019 | 5/2/2020 | 2019 | NEW |
| P/07/MTS/2019/0015 | - | 6/1/2019 | 5/2/2020 | 2019 | renew |
| P/07/MTS/2019/0159 | - | 6/1/2019 | 5/2/2020 | 2019 | renew |
| P/07/MTS/2019/009 | - | 19-08-2019 | 5/2/2020 | 2019 | renew |
| 07/MT/2017/49 | MT/492678 | 17-12-2018 | 16-01-2020 | 2018 | lost |
| CAR/131094 | 0 | 19-09-2019 | 15-05-2020 | 2015 | lost |
first 4 records fromdate is 06-01-2019 and today is 22-10-2019 means 12 months is not complete so all 4 records considered as "new" ..
check second last record from date is 17-12-2018 and today is 22-10-2019 duration is above 12 months so means these type of records considered as "renew"
check last record from date is 19-09-2019 and today is 22-10-2019 means duration is under 12 months but to date is greater than today date so last record also renew ..
i used this condition
if( Date(fromdate)=Min_Date and date(todate) > date(today()) ,'NEW',
if(date(todate) > date(today()),'renew','lost')) as STATUS
lost records are correctly displayed problem is in "New", "Renew" so how to solve this
check attached qvd
