Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

venkatg6759
Contributor III

Rounding timestamp to hour

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

37 Replies
mov
Esteemed Contributor III

Re: Rounding timestamp to hour

Try

round(readtimestamp%, 1/24)

venkatg6759
Contributor III

Re: Rounding timestamp to hour

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

mov
Esteemed Contributor III

Re: Rounding timestamp to hour

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?

preminqlik
Valued Contributor II

Re: Rounding timestamp to hour

Load *,

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

from path ;

hope this helps

venkatg6759
Contributor III

Re: Re: Rounding timestamp to hour

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

Not applicable

Re: Rounding timestamp to hour

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

venkatg6759
Contributor III

Re: Rounding timestamp to hour

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

mov
Esteemed Contributor III

Re: Rounding timestamp to hour

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

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

MVP
MVP

Re: Rounding timestamp to hour

Hi venkata,

this must be

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

- Ralf