topic Trouble with Dates in the future !! in QlikView

Trouble with Dates in the future !!

timsaddler — Fri, 14 Feb 2014 16:53:52 GMT

When i force a date using the Addmonths function the data doesnt join as the Function seems to return decimal places.

Example

Addmonths(Date(MonthEnd(today())+1),0) seems to return 41699.999999988

To join to all my other date i guess it needs to be an Integer

How do i acheive this?

Re: Trouble with Dates in the future !!

alexandros17 — Fri, 14 Feb 2014 16:54:44 GMT

write

Date(Addmonths(Date(MonthEnd(today())+1),0) )

Re: Trouble with Dates in the future !!

rbecher — Fri, 14 Feb 2014 17:00:42 GMT

Strange, maybe better with Floor(Today()) ?

Re: Trouble with Dates in the future !!

Nicole-Smith — Fri, 14 Feb 2014 20:44:09 GMT

It's because monthend() returns the last date and the last possible time of the month:

MonthEnd(date [, shift = 0])

Returns a value corresponding to a timestamp with the last millisecond of the last date of the month containing date. The default output format will be the DateFormat set in the script. Shift is an integer, where the value 0 indicates the month which contains date. Negative values in shift indicate preceding months and positive values indicate succeeding months.

Examples:

monthend ( '2001-02-19' ) returns '2001-02-28' with an underlying numeric value corresponding to '2001-02-28 23:59:59.999'

monthend ( '2001-02-19', -1 ) returns '2001-01-31' with an underlying numeric value corresponding to '2001-01-31 23:59:59.999'

You need to use floor() like Ralf Becher suggested, but it needs to be around the monthend() function instead of the today() function:

Addmonths(Date(Floor(MonthEnd(today()))+1),0)

I'm also not sure why you're using the AddMonths() function if you're not adding or subtracting any months...

Re: Trouble with Dates in the future !!

jaimeaguilar — Sat, 15 Feb 2014 00:08:10 GMT

Just adding to what's already been said, I think the problem is the order of the functions, you typed this:

Addmonths(Date(MonthEnd(today())+1),0)

The thing here is that Date would be placed first, because no matter what tranformations you make to today(), at the end you want to get a date. Also is not the same to add a month and then getting the end of the month than getting the end of the month and then adding a month:

today() = february 14 -> date(MonthEnd(addmonths(today(), 1)) will return march 31st (31/03/2014 asuming date is set to DD/MM/YYYY)

today() = february 14 -> date(Addmonths(MonthEnd(Today()), 1)) will return march 28th (28/02/2014 assuming date is set to DD/MM/YYYY)

Regards

Re: Trouble with Dates in the future !!

rbecher — Sat, 15 Feb 2014 20:20:48 GMT

I'm still not sure what you're trying to get here. Perhaps the beginning of the next month?

=MonthStart(AddMonths(Today(),1))