topic Re: Date variable showing incorrect date after script loaded in QlikView

Date variable showing incorrect date after script loaded

crystles — Wed, 20 Jan 2016 15:55:02 GMT

I am trying to create a dynamic formula to subtract two dates and get the number of days between them. One date is static, and one is the current date.

I have tried just subtracting them and that gives me a number over 40,000. Which is incorrect.

So now I am trying to create the dates in the load script as variables and just subtract the variables. It looked like it was working, but when I looked at the Variable in the Settings->Variable Overview, it showed a completely different date.

Here is the code in my script.

Let vCurrentTodayDate = Date(Today(0), 'MM-DD-YYYY');
Let vAnnualizedDate = Date(01-02-2016, 'MM-DD-YYYY');

In the variable overview, it has the correct date of today for the first variable (01-20-2016)

But the second variable is showing another date then the one entered in the script (06-22-1894) I'm not sure why it's showing me a number from 1894?

Re: Date variable showing incorrect date after script loaded

sunny_talwar — Wed, 20 Jan 2016 15:56:50 GMT

May be put the date within single quotes:

Let vAnnualizedDate = Date('01-02-2016', 'MM-DD-YYYY');

Re: Date variable showing incorrect date after script loaded

settu_periasamy — Wed, 20 Jan 2016 16:05:19 GMT

sunindia‌ small correction ('#' is missing)

Let vAnnualizedDate = Date#('01-02-2016', 'MM-DD-YYYY'); //This will give date number

Let vAnnualizedDate = Date(Date#('01-02-2016', 'MM-DD-YYYY')); //This will give date

Re: Date variable showing incorrect date after script loaded

sunny_talwar — Wed, 20 Jan 2016 16:08:09 GMT

Wouldn't Date#() depend on the fact what is used in the SET DateFormat?

If it is set as MM-DD-YYYY, the wouldn't it automatically read it '01-02-2016 as date? I was the under the impression it would, but I might be wrong here

I am going to test it now

Re: Date variable showing incorrect date after script loaded

sunny_talwar — Wed, 20 Jan 2016 16:11:14 GMT

This seems to work:

SET DateFormat='MM-DD-YYYY';

LET vAnnualizedDate = Date('01-02-2016', 'MM-DD-YYYY');

Re: Date variable showing incorrect date after script loaded

Wed, 20 Jan 2016 16:13:35 GMT

If the variable return 1894 means, there something missing on the variable. Because you didn't add the single quotes so it takes 1 as value for variable and return respective date.

Let vAnnualizedDate = Date('01-02-2016', 'MM-DD-YYYY');

Re: Date variable showing incorrect date after script loaded

settu_periasamy — Wed, 20 Jan 2016 16:18:09 GMT

Agreed. SET DateFormat doesn't came in my mind. sorry about that. 🙂

Re: Date variable showing incorrect date after script loaded

sunny_talwar — Wed, 20 Jan 2016 16:20:24 GMT

But you were right, I simply assumed the SET DateFormat to be MM-DD-YYYY.

Re: Date variable showing incorrect date after script loaded

crystles — Wed, 20 Jan 2016 16:21:07 GMT

Thank you for explaining why it was giving me such a strange date. That makes sense now.

Re: Date variable showing incorrect date after script loaded

crystles — Wed, 20 Jan 2016 16:21:38 GMT

I knew it had to be something simple I was missing. I added the single quotes around my date and it worked perfectly.

Thank you!

Re: Date variable showing incorrect date after script loaded

sunny_talwar — Wed, 20 Jan 2016 16:22:18 GMT

Awesome

Glad we all were able to help