topic Re: Help on Date comparission in QlikView

Help on Date comparission

Thu, 26 Dec 2013 13:36:40 GMT

Hi All,

I am doing some manipulations.

I have a date filed which is of timestamp. So i have converted into Date(Date field)

When i am doing Today() - Date(Date field) it is not giving result as expected.

Example IF

Date(Date field) = 25-12-2013 the output is 0

Date(Date field) = 26-12-2013 the output is -0

And important thing is when Today is 01-01-2014 and Date(Date field) = 31-12-2013 it is giving -20.

Can some one tell me where i am doing wrong?

Re: Help on Date comparission

Gysbert_Wassenaar — Thu, 26 Dec 2013 13:43:36 GMT

I have a date filed which is of timestamp

That's where your problem is. Look at the output of num(Date Field). You'll see that there is a fraction, which is the time part of the date time. If you want to calculate using the date parts only you need to use the floor function to cut of the time part: today() - floor(Date Field)

And important thing is when Today is 01-01-2014 and Date(Date field) = 31-12-2013 it is giving -20.

That sounds like the string of the date is interpreted as numbers:

1 -1 - 2014 = -2014

31 - 12 - 2013 = -1994

-2014 - -1994 = -2014 + 1994 = -20

Re: Help on Date comparission

Thu, 26 Dec 2013 14:11:22 GMT

Hi Gysbert,

Floor function is solving my problem.

What about the second case ?

When Today = 1 -1 - 2014

and Floor(Datefield ) = 31 - 12 - 2013

Will it give -20 or 1?

Re: Help on Date comparission

Gysbert_Wassenaar — Thu, 26 Dec 2013 15:07:51 GMT

No idea. I can't read minds so I have no idea what you're actually doing. You can't simply use a string 31-12-2013 and expect it to be treated as a date. Use the date# function to create a date from a string or use the makedate function: makedate(2013,12,1).