Condition according to dates

capriconuser — Tue, 22 Oct 2019 12:07:13 GMT

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

topic Condition according to dates in QlikView

Condition according to dates