topic Re: Floor Time not working in QlikView

Floor Time not working

Fri, 24 Jun 2016 09:46:56 GMT

Hi Guys,

I have an excel spreadsheet with approx 6000 rows with timestamp. The time is American time so I have to add 14 hours in that field to convert it to my local timestamp.

I am using following in the load script :

Date(timestamp(Date + 14/24)) AS Date,

Time(timestamp(Date + 14/24)) AS Time,

Problem:

When I crate a bar chart with time as a dimension then it shows me all the time from every transaction.

What I am trying to achieve?

I only want the chart to have 24 hours in ( AM / PM Format). How can I get that?

I have attached sample QVW and Spreadsheet.

Thank you.

Re: Floor Time not working

swuehl — Fri, 24 Jun 2016 09:53:05 GMT

Try

Time(frac(Date + 14/24)) AS Time,

timestamp(Date + 14/24) AS Timestamp,

Date(floor(Date + 14/24)) AS Date,

Re: Floor Time not working

Fri, 24 Jun 2016 10:04:57 GMT

Thank swuehl‌

But it still not working as i expected.

I have attached QVD here with what you advised.

Thanks

Re: Floor Time not working

swuehl — Fri, 24 Jun 2016 10:10:03 GMT

Try

LOAD Date(timestamp(Date + 14/24)) AS Date,

Time(Floor(frac(Date + 14/24),1/24),'h tt') AS Time,

Tokens

FROM

...

Re: Floor Time not working

marcus_sommer — Fri, 24 Jun 2016 10:53:52 GMT

An alternatively could be the function: ConvertToLocalTime().

- Marcus