topic Rounding timestamp to hour in QlikView

Rounding timestamp to hour

venkatg6759 — Wed, 11 Jun 2014 18:26:23 GMT

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

Re: Rounding timestamp to hour

Anonymous — Wed, 11 Jun 2014 18:33:46 GMT

Try

round(readtimestamp%, 1/24)

Re: Rounding timestamp to hour

venkatg6759 — Wed, 11 Jun 2014 18:35:43 GMT

Nope thats giving me the same no of reads per the day. I want to show reads per each hr since each read has a timestamp

Re: Rounding timestamp to hour

Anonymous — Wed, 11 Jun 2014 18:42:55 GMT

Not clear...

Number of reads per time period doesn't depend on rounding. If it is in the front end chart, timestamp rounded per hour (date-hour) should be your dimension. If it is in the script, you have to group by date-hour.

Can you upload an example of your app?

Re: Rounding timestamp to hour

preminqlik — Wed, 11 Jun 2014 18:45:45 GMT

Load *,

timestamp#(left(Timestamp,10)&' '&MakeTime(left(right(Timestamp,8),2)),'DD/MM/YYYY HH:MM:SS') as Roundtimestamp

from path ;

hope this helps

Re: Re: Rounding timestamp to hour

venkatg6759 — Wed, 11 Jun 2014 18:48:50 GMT

I am currently looking to keep the solution in the front end in dimension.

May be the attached sample give you an idea .

Thanks,

Venkata

Re: Rounding timestamp to hour

Wed, 11 Jun 2014 18:52:11 GMT

=subfield(DateTime, ' ', 1)& ' ' & time(round(time#(subfield(DateTime, ' ', 3), 'hh:mm:ss'), 1/24), 'hh:mm:ss')

Re: Rounding timestamp to hour

venkatg6759 — Wed, 11 Jun 2014 19:02:43 GMT

This looks better solution. Can you help me to write in front end .

Re: Rounding timestamp to hour

Anonymous — Wed, 11 Jun 2014 19:07:43 GMT

If on the front end, use this calculated dimension instead of your current dimension:

=timestamp(round([Consumption Timestamp], 1/24), 'MM/DD/YYYY hh')

Re: Rounding timestamp to hour

rbecher — Wed, 11 Jun 2014 19:17:27 GMT

Hi venkata,

this must be

timestamp(round(readtimestamp% * 24) / 24) as readtime

- Ralf

Re: Rounding timestamp to hour

eduardo_sommer — Wed, 11 Jun 2014 19:31:19 GMT

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

Re: Rounding timestamp to hour

rogerioqv — Wed, 11 Jun 2014 19:34:09 GMT

Try:

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

Re: Rounding timestamp to hour

venkatg6759 — Wed, 11 Jun 2014 19:55:23 GMT

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 .

Re: Rounding timestamp to hour

eduardo_sommer — Wed, 11 Jun 2014 20:03:08 GMT

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

Re: Rounding timestamp to hour

rogerioqv — Wed, 11 Jun 2014 20:07:20 GMT

Try:

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

Re: Re: Rounding timestamp to hour

venkatg6759 — Wed, 11 Jun 2014 20:54:37 GMT

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

Re: Re: Rounding timestamp to hour

rogerioqv — Wed, 11 Jun 2014 20:57:37 GMT

Friend,

Have you tried this?

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

Re: Rounding timestamp to hour

venkatg6759 — Wed, 11 Jun 2014 21:00:30 GMT

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

Re: Rounding timestamp to hour

rogerioqv — Wed, 11 Jun 2014 21:08:03 GMT

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>

Re: Rounding timestamp to hour

johnca — Wed, 11 Jun 2014 21:32:15 GMT

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