time rounding to a new date, but data does not shift with it...

[#1]

Hi,

I have the following problem. I have a dataset which as a record every hour. unfortunately the recorded time shifts from the format 23:00:00 and 24:00:00 to 23:59:59 during the month of April and this every year. But 23:59:59 is already 00:00:00 of the next day. So once a year during this particular month I get 25 records for a day and all my records are shiftet 1 hour from there on...

I have now rounded my time with the following: timestamp(round(Dato, 1/24), 'DD-MMM-YYYY hh:mm') as Time_Data and it actually worked fine, BUT somehow the datainfromation is not shiftet to the next day.

I have attached a screenshot, which illustrates my problem. I have a master calendar which I use to look at my data and when I choose the 10th of April (in this case) I get 25 records, instead that it shows only the ones for the 10th...

Does anyone know what to do in such a case?

Thanks a lot, Barbara

[Anonymous]

Hi Barbara,

Rounding to hour, when your timestamp is great than 30 minutes, it will be one more hour.

Try with this:

TimeStamp(Date(Floor(Dato))+Hour(Dato),'DD-MMM-YYYY hh:mm')

Regards!

[Siva_Sankar]

Smaller or equal too calculating with times

Correct Time Arithmetic | Qlikview Cookbook

[Anonymous]

Please, check the correct answer.

thank you