How to use SQL Dateadd function in Qlik

[ashishpalkar]

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.

[vamsee]

Try the AddMonths() or AddYears() function in QlikView,

You can also add dates directly Date(Today() + 30) == 30 Days from today

[ashishpalkar]

Can anyone suggest how to execute this statement in Qlik?

ISNULL(I.maturitydate,I.ExpiryDate) between @BusinessDate AND DATEADD(d,@DaystoMaturity,@BusinessDate)

Thanks

[vamsee]

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)

[alexandrakfcosta]

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

[ashishpalkar]

Thanks Alexandra , I will check your suggestions.