Qlik Community

Ask a Question

New to QlikView

If you’re new to QlikView, start with this Discussion Board and get up-to-speed quickly.

Announcements
Talk to Experts Tuesday, January 26th at 10AM EST: Qlik Sense. REGISTER NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Contributor III
Contributor III

Filter table with date greater than within script

Hi all,

I'm trying to load the past three months data from a QVD file, however I can't get my WHERE clause to work.

Here's my script:

Let v3MonthsAgoToday = AddMonths(Date(Today()),-3);

LOAD Audit_Id,

     Date(Audit_Timestamp) AS Audit_Timestamp,

     Value

FROM

Application_Audit.QVD (qvd)

WHERE Audit_Timestamp >= v3MonthsAgoToday;

I've tried surrounding both v3MonthsAgoToday and Audit_Timestamp with Date(), Date#() and Date([field/variable], 'dd-mm-yyyy') but none of these work.  The only thing that works is WHERE Audit_Timestamp >=  42263, however I need this date to change relative to today's date.  I'd rather get rid of the v3MonthsAgoToday variable if possible too.

Any responses much appreciated, thank you!

1 Solution

Accepted Solutions

Hi Sarah, try with this variable value:

Let v3MonthsAgoToday = Num(AddMonths(Date(Today()),-3));

If doesn't works use the variable in where like:

WHERE Audit_Timestamp >= $(v3MonthsAgoToday);

View solution in original post

5 Replies

Hi Sarah, try with this variable value:

Let v3MonthsAgoToday = Num(AddMonths(Date(Today()),-3));

If doesn't works use the variable in where like:

WHERE Audit_Timestamp >= $(v3MonthsAgoToday);

View solution in original post

Not applicable

Answer above should work if the field in your where clause is formatted as a number and the variable is in date format.

However if this doesn't fix your issue could you post an simple app example.

Contributor III
Contributor III

Thank you Ruben, I knew it would be something simple!  Adding num() to the variable fixed it.

Champion
Champion

try like this:

Let v3MonthsAgoToday = date(AddMonths(Today(),-3),'DD/MMM/YYYY');

LOAD Audit_Id,

     Date(Audit_Timestamp,'DD/MMM/YYYY') AS Audit_Timestamp,

     Value

FROM

Application_Audit.QVD (qvd)

WHERE Audit_Timestamp >= '$(v3MonthsAgoToday)';

Hope this will work!!

Yes, a date from qvd is also a number, so filter as number works.

The solution provided by Balraj also should work. Setting the date in the where clause between simple quotes keeps the string, without the simple quotes QV tries to resolve as a math operation making a division (with '/' as separator xx/xx/xxxx) or the difference (with '-' as separator xx-xx-xxxx).