Floor Time not working

[#1]

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.

[swuehl]

Try

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

timestamp(Date + 14/24) AS Timestamp,

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

[#3]

Thank swuehl‌

But it still not working as i expected.

I have attached QVD here with what you advised.

Thanks

[swuehl · Accepted solution]

Try

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

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

     Tokens

FROM

...

[marcus_sommer]

An alternatively could be the function: ConvertToLocalTime().

- Marcus