Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
cuvupaxi
Contributor
Contributor

Condition according to dates

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 this great sites with cam naked girls:

https://livesexcams.cc/

https://sexcams.fans/

https://freelivesexcams.me

 

 

 

 

 

Labels (1)
1 Reply
Marijn
Creator II
Creator II

I added/changed some stuff in red:

if( Date(fromdate) <= Min_Date and date(todate) < date(today()) ,'NEW',
if( Date(fromdate) > Min_Date and date(todate) > date(today()) ,'renew','lost')) as STATUS