topic Date in QlikView

Date

easternfish — Thu, 16 Dec 2010 22:53:32 GMT

HI,

I need my data to open with yesterday's date.

XDATE format is yyyymmdd

how can i make xdate = today -1

DATE(XDATE) = DATE(today(),'yyyymmdd') -1

thanks

Date

oscar_ortiz — Thu, 16 Dec 2010 23:02:15 GMT

Try this

DATE(today()-1,'yyyymmdd')

Date

easternfish — Thu, 16 Dec 2010 23:19:55 GMT

thanks that fixed 1 problem that i had but i'm still getting errors.

here's what i got

SQL##f - SqlState: 37000, ErrorCode: 4294964196, ErrorMsg: [Microsoft][ODBC Microsoft Access Driver] Syntax error in date in query expression 'DATE#(XDATE) = DATE(today()-1,'yyyyMMdd')'.
SQL SELECT *
FROM `XFILE`
WHERE DATE#(XDATE) = DATE(today()-1,'yyyyMMdd')

this works :

where XDATE = 20101215;

Date

oscar_ortiz — Thu, 16 Dec 2010 23:40:57 GMT

Ok, try this:

Create a variable first, then use the variable in your where clause.

Let vYesterday = Date(Today()-1, 'YYYYMMDD');

Now in your where clause use

WHERE DATE#(XDATE) = $(vYesterday)

Date

easternfish — Fri, 17 Dec 2010 20:16:48 GMT

Great Thanks

this works!

Let vYesterday = Date(Today()-1, 'YYYYMMDD');

WHERE XDATE = $(vYesterday);

Date

oscar_ortiz — Fri, 17 Dec 2010 20:25:27 GMT

No problem...

As a side note, if you needed to have the variable equal to a string value as in:

where XDATE = '20101215'

You could use the variable wrapped in single quotes...

where XDATE = '$(vYesterday)'