Qlik Community

Ask a Question

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

Do More with Qlik - for Beginners and Beyond, Topic: Qlik Replicate on January 21, 2PM EST. REGISTER NOW
Showing results for 
Search instead for 
Did you mean: 
Contributor II
Contributor II

Convert time string an round time


i will convert date and time in the right format.

strTime: 10.02.2014 07:45:50

10.02.2014 -> Date(Left(strTime,10),'DD.MM.YYYY') AS DAT,

07:45:50 -> Time#(Right(strTime,8),'HH.MM.SS') AS TIM


07:45:50 -> Time(Right(strTime,8)) as TIM

Now, strTime should be round to next 10 min, e.g. 07:50:00. I've check a formula in excel, but in QlikView it's no working.

07:50:00 -> RUNDEN(TIM*24/(1/6);0)*(1/6)/24

Do you have any idea, whats wrong?


3 Replies
Not applicable

Hi Stefan

I'd rather use Floor() and Frac() to extract day and time part from the timestamp.

When using Frac() you can specify nearest number to round to. 1 / 24 / 6 is going to round "roughly" to closest 10 minutes. By "roughly" I mean rounding errors described here community.qlik.com/blogs/qlikviewdesignblog/2013/12/17/rounding-errors


I would not use string functions for this. Hence, I would use

Date(Floor(TimeStamp#(strTime,'DD.MM.YYYY hh:mm:ss')),'DD.MM.YYYY') AS Date,

Time(Frac(TimeStamp#(strTime,'DD.MM.YYYY hh:mm:ss')),'hh:mm:ss') AS Time,  // (seconds)

Time(Ceil(Frac(TimeStamp#(strTime,'DD.MM.YYYY hh:mm:ss')),10/24/60),'hh:mm') AS Time10m, // (10-min interval)


Contributor II
Contributor II

Hi Henric,

thanks for your reply and your great solution.