topic Re: how to get the last second on a given year in QlikView

how to get the last second on a given year

Wed, 25 Nov 2020 16:16:04 GMT

Hi,

I need to calculate the last second of a given year and represent it as a full date+time dd/mm/yyyy hh:mm:ss

in order to compare this date+time to other given date+time in my model...

how can i create a full date+time value from a given year...

for example:

given year: 2005

i neet to change it to 31/12/2005 23:59:59

Thank you.

Re: how to get the last second on a given year

martinpohl — Mon, 04 Apr 2016 14:30:12 GMT

like this

Regards

Re: how to get the last second on a given year

sunny_talwar — Mon, 04 Apr 2016 14:30:21 GMT

May be this:

=Timestamp(YearEnd(MakeDate(2015)))

Replace MakeDate(2015) with your Date Field or leave it static based on your requirement

Re: how to get the last second on a given year

Colin-Albert — Mon, 04 Apr 2016 14:32:57 GMT

The YearEnd() function will return the value you need.

YearEnd( date [, shift = 0 [, first_month_of_year = 1]])

Returns a value corresponding to a timestamp with the last millisecond of the last date of the year containing date. The default output format will be the DateFormat set in the script. Shift is an integer, where the value 0 indicates the year which contains date. Negative values in shift indicate preceding years and positive values indicate succeeding years. If you want to work with (fiscal) years not starting in January, indicate a value between 2 and 12 in first_month_of_year.

Re: how to get the last second on a given year

swuehl — Mon, 04 Apr 2016 14:34:34 GMT

Take care that YearEnd returns the last millisecond of the year,even when formatted only up to seconds.

Just in case you are perfoming a match with a real second based timestamp.

edit:

Timestamp#(Timestamp(YearEnd(MakeDate(2015))))

Re: how to get the last second on a given year

Mon, 04 Apr 2016 15:21:48 GMT

=Timestamp(MakeDate(2005+1) - 1/86400)

Subtracts one second from the next year and therefore gives the last second of this year.

Re: how to get the last second on a given year

Mon, 04 Apr 2016 15:46:56 GMT

Thank you all

Re: how to get the last second on a given year

swuehl — Mon, 04 Apr 2016 15:49:42 GMT

Nice idea, Do Kra!

I just want to emphasize that comparing floating point values, like Timestamps from a source and timestamp values created in the Qlik script is error prone (though not sure if the OP is really comparing floating point values):

Correct Time Arithmetic | Qlikview Cookbook‌

Hence it's good practice to use the same kind of number (re-)generation on both sides.