Rounding timestamp to hour

[venkatg6759]

Hello Everyone,

I have a field called readtimestamp% . we have some books where people read we get timestamp. we need to find how many people read per  hour on a day.

It has something like

02/02/2014  01:07:00  

02/02/2014  00:00:14

How can i round them to

02/02/2014  01:00:00

02/02/2014  00:00:00

Thanks,

Venkata

[eduardo_sommer]

Try it this way:

Create a straight table with a Calculated Dimension, Use the expression

     =date(floor([Consumption Timestamp],1/24),'DD/MM/YYYY hh:mm:ss')

for the dimension. Change the formating to your region format.

For the expression, use:

      =Count([%Contact GUID])

It worked for me. Observe that I use floor instead of round.

Eduardo

[rogerioqv]

Try:

=DATE(vDateHour, 'DD/MM/YYYY HH:00:00')

[venkatg6759]

Thankyou

but i have a date when we select a date it will show all the timestamps in a day .But here when i select a date it was giving me reads per day instead of hour .

[eduardo_sommer]

You can create a new filter (list) with an expression instead of a field. Use the following expression>

     =date(floor([Consumption Timestamp]))

When you filter a date in this list, the other table will show the counts hour-by-hour of that date.

Eduardo

[rogerioqv]

Try:

=DATE(vDateHour, 'DD/MM/YYYY HH:00:00')

[venkatg6759]

I tried all the above solutions but unable to get the desired result .

Check the screenshot

It was divided as per the hour but there is no variation in the graph.It shows same reads as per day instead of hour .

Any other solutions or help

[rogerioqv]

Friend,

Have you tried this?

=DATE(vDateHour, 'DD/MM/YYYY HH:00:00')

[venkatg6759]

Yup but same result .need to find a way how can i calculate per hour.

[rogerioqv]

You can share qvw?

2014-06-11 18:00 GMT-03:00 venkatg6759 <qcwebmaster@qlik.com>:

Qlik Community <http://community.qlik.com/> Rounding timestamp to

hour

reply from venkatg6759

<http://community.qlik.com/people/venkatg6759?et=watches.email.thread> in *App

Development* - View the full discussion

<http://community.qlik.com/message/546804?et=watches.email.thread#546804>

[johnca]

I believe the solution is above. I took liberties and utilized a variation of Michael's script (Liked). It is not clear if your dates are month/day/year or day/month/year, but that doesn't matter. Just change the sequence of MM and DD.

TimeStamp(Round(vDateHour,1/24),'MM/DD/YYYY hh:00:00') as Rounded_to_Hour1

Using Rogerio's format seemed to retain the minutes.

HTH,

John