37 Replies Latest reply: Jun 4, 2015 12:06 PM by prathap budati

# 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

Try

• ###### 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

• ###### 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.

• ###### 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

• ###### 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')

• ###### Re: Re: Re: Rounding timestamp to hour

Have you tried your "TRY" with my suggestion?  See attached.

• ###### Re: Rounding timestamp to hour

Try:

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

• ###### Re: Rounding timestamp to hour

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

from path ;

hope this helps

• ###### Re: Rounding timestamp to hour

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

• ###### Re: Rounding timestamp to hour

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

• ###### Re: Rounding timestamp to hour

Hi venkata,

this must be

- Ralf

• ###### Re: Rounding timestamp to hour

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

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

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

Try:

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

• ###### Re: Re: Rounding timestamp to hour

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

Friend,

Have you tried this?

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

• ###### Re: Rounding timestamp to hour

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

• ###### Re: Rounding timestamp to hour

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

• ###### Re: Rounding timestamp to hour

I suggest you to change the round() function to floor(). Look in your example that 8:35 pm is being rounded to 21 instead of 20.

Eduardo

• ###### Re: Rounding timestamp to hour

Yes, but it is > halfway to the next hour. The original example did not go beyond the 30 minute point. If that is the original author's intent than yes, use floor() instead of round().

Good point.

• ###### Re: Rounding timestamp to hour

I am able to get the timestamp rounded to Hour, but the problem here is it was giving me straight line (reads per day)

Let me paste the script for you .

FROM

Qualify *;

ConsumptionCalendar:

Year,

//MaxYear,

Quarter,

Month,

Day,

//[Week Day],

//Hour,

//Minute,

[%TIMESTAMP],

//[Month Id],

//[Month and Year],

[Fiscal Week]

//[YTD Flag],

//[QTD Flag],

//[MTD Flag],

//[WTD Flag],

//[T12M Flag],

//[T6M Flag],

//[T8W Flag]

from [\\firmwide.corp.gs.com\irroot\Projects\NY\BI\Qlikview\qvd\General\Calendar.QVD] (qvd);

Timestamp coming from calender Qvd which is linked by Date

• ###### Re: Rounding timestamp to hour

Three things...

What is the format of your field [Consumption Date]? Post an example of what an actual [Consumption Date] value is.

I suspect your calendar has all dates, but not all minutes of each hour for each day, and your data does.

What if you just format the date field in your data as suggested rather than try to link a calendar? What does it look like?

• ###### Re: Rounding timestamp to hour

Hi,

Above solution may answer to your question, but I just want to share something with you.

***If your field is string data type, then we use:

=Date(Date#('02/02/2014 01:07:00','dd/MM/yyyy hh:mm:ss'),'dd-MM-yyyy hh:00:00') -> Return 02-02-2014 01:00:00

=Date(Floor(Date#('02/02/2014 01:07:00','dd/MM/yyyy hh:mm:ss')),'dd-MM-yyyy') -> Return 02-02-2014

=Time(Floor(Frac(Date(Date#('02/02/2014 01:07:00','dd/MM/yyyy hh:mm:ss'),'dd-MM-yyyy hh:00:00')),1/24),'hh:mm:ss') -> Return 01:00:00

***If your field is timestamp data type, then we can use

=Date(TimeStampField,'dd-MM-yyyy hh:00:00') -> Return 02-02-2014 01:00:00

=Date(Floor(TimeStampField),'dd-MM-yyyy') -> Return 02-02-2014

=Time(Floor(Frac(Date(TimeStampField,'dd-MM-yyyy hh:00:00')),1/24),'hh:mm:ss') -> Return 01:00:00

Regards,

Sokkorn

• ###### Re: Re: Rounding timestamp to hour

I posted the qvw.How can i get the graph show variation as per hour.

Can someone help me out.

Thanks,

Venkata

• ###### Re: Rounding timestamp to hour

Hi, Venkata

The problem is in your data model. The table that contains the [Reads by Contact] field has a day key, while you are trying to aggregate by a more granular dimension that exist only in the other table.

You need to maintain yor [Reads by Contact] in a timestamp level and link the tables also by the timestamp field.

Eduardo

• ###### Re: Rounding timestamp to hour

Can you suggest me how can i achieve that since i don't have timestamp in the consumption QVD .

Any method that would be possible ?

• ###### Re: Rounding timestamp to hour

Venka,

You can send me a sample of the two QVD's? I believe it would be easier to mount something.

• ###### Re: Re: Rounding timestamp to hour

Thanks for your help.check the sample qvds iam sending them in excel .You can try to link them

Thanks,

Venkata

• ###### Re: Rounding timestamp to hour

Hi, Venkata

You need to get your consumption with date and time. In your data, you have only date.

Eduardo

• ###### Re: Re: Rounding timestamp to hour

Venka,

Unfortunately, upon receiving the worksheets, I found that you need to create the timestamp in the table consumption ... field without this information we will not get the amount by date / time.

• ###### Re: Rounding timestamp to hour

Thank you so much for everyone for all the efforts.

I understand that without timestamp in the table we cannot join to calender and get it by renaming it.

Thanks,

Venkata

• ###### Re: Re: Rounding timestamp to hour

Your Date field does not contain the hours. You cannot create what is not there (in this case).

Does your data even have a field where the timestamp contains the time of day?

• ###### Re: Re: Re: Rounding timestamp to hour

Venkata,

The problem is in the format of date / time. Check the script so you get the correct format to be able to apply the expression.

See the attached image.

• ###### Re: Rounding timestamp to hour

The only thing you can sort on is the day itself. If you do not have the hour of day in your data model, you cannot do what you want. When I look at your data in the table viewer, it shows only the date, and does not contain the hour (and minutes, seconds) of each reader. Yuo need these to do what you want.

• ###### Re: Rounding timestamp to hour

• Avg. Time Spent by Employee on Floor
• Calculate Team Utilization (use formula -Total time/Number of days per activity ) solve any body??