topic Date Variable in QlikView

Date Variable

danielact — Tue, 23 Aug 2011 13:12:35 GMT

I am having some trouble with something that I've done in the past with no problem - but on my current project, it doesn't seem to be working.

I have 2 fields, Start Date and End Date. I have a variable called vDate, with an input box for it. When you enter a date, triggers should set Start Date <= vDate, and End Date >=vDate. This should give you all policies that are effective on the selected date.

As I said, I did this before with no problem. The only difference with this particular project that I can see is that the date fields in my current data also have a time at the end - so it's not just 1/1/2011, it's 1/1/2011 12:00:00 AM.

Could the time be messing me up? Here is the text of my triggers, which has worked perfectly before:

Select in Field Start Date:

=if(vtDate > 0,'<=' & date(vtDate, '$(DateFormat)'))

Select in Field End Date:

=if(vtDate > 0,'>=' & date(vtDate, '$(DateFormat)'))

Date Variable

danielact — Tue, 23 Aug 2011 13:14:46 GMT

Oh, and one other thing: my DateFormat is set to 'M/D/YYYY'

Re: Date Variable

Anonymous — Tue, 23 Aug 2011 13:22:48 GMT

your start and end dates are in timestamp format (date + time)

you should either convert these field to date fields in de sql script with th Date function

or convert your variable to a timestamp by adding the time to the variable

I think the first option is the most easy, unless you also want to test the start and end time of your policy

Date Variable

its_anandrjs — Tue, 23 Aug 2011 13:48:32 GMT

Hi,

You need to pull date from Date Column by set as Date(Datecolumn,MM/DD/YYYY) as NewDatecolumn, I thing it is no problem if you use date with timestamp. If you have any sample please share it.

Regards,

Anand

Date Variable

danielact — Tue, 23 Aug 2011 13:50:51 GMT

Thanks, got it working!