Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi There
I am using following SQL statement and want to use same in Qlik. Any suggestions how to use Dateadd in Qlik. In below example user is inserting random date which will be added to selected business date and results will be shown.
ISNULL(I.maturitydate,I.ExpiryDate) between @BusinessDate AND DATEADD(d,@DaystoMaturity,@BusinessDate)
Thanks in advance.
Try the AddMonths() or AddYears() function in QlikView,
You can also add dates directly Date(Today() + 30) == 30 Days from today
Can anyone suggest how to execute this statement in Qlik?
ISNULL(I.maturitydate,I.ExpiryDate) between @BusinessDate AND DATEADD(d,@DaystoMaturity,@BusinessDate)
Thanks
try
ALT(Maturitydate, ExpiryDate)> =BusinessDate
and ALT(Maturirydate, ExpiryDate) <= Date(BusinessDate + DaystoMaturiy)
/************************ or **********************/
IF(Len(Trim(Maturitydate))<1, ExpiryDate, Maturitydate)> =BusinessDate
and IF(Len(Trim(Maturitydate))<1, ExpiryDate, Maturitydate) <= Date(BusinessDate + DaystoMaturiy)
Hi, Ashish.
I think what you are looking for is:
If(IsNull(MaturityDate), ExpiryDate, MaturityDate) >= BusinessDate AND
If(IsNull(MaturityDate), ExpiryDate, MaturityDate) <= Date(BusinessDate + DaystoMaturity);
Be careful about manipulating dates. Be sure that all the fields and variables involved are typed as date.
If not sure, use the function Date in each of the fields.
This is how I tested the code in Qlik:
LET ExpiryDate = Today();
LET MaturityDate = null(); // Date(Today() + 10);
LET BusinessDate = Date(Today() - 20);
LET DaystoMaturity = 30;
LET Test = If(IsNull(MaturityDate), ExpiryDate, MaturityDate) >= BusinessDate
AND If(IsNull(MaturityDate), ExpiryDate, MaturityDate) <= Date(BusinessDate + DaystoMaturity);
I hope I could help.
Best,
Alexandra Costa
Thanks Alexandra , I will check your suggestions.