Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Join us for a live Q&A! September 21, 10 AM ET - Onboarding Fast in Qlik Sense SaaS! REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
bfournet
Partner
Partner

Time rouding

Hello,

I would like to round time this way :

From original timestamp
Half hour
QuarterTime by 5 minutes steps
21:18:0021:0021:1521:15
21:39:0021:3021:3021:35
21:56:0021:3021:4521:55

Rounded values should never be superior to originale timestamp.

So possible values can be :

     * Half hour -> 21:00, 21:30, 22:00, 22:30 ...

     * Quarter -> 21:00, 21:15, 21:30, 21:45 ...

     * Time by 5 minute step -> : 21:00, 21:05, 21:10, 21:15 ...

I created a master calender this way (Field "Horodatage" is a timestamp) :

Calendrier:

NoConcatenate

LOAD Distinct

    Horodatage, // Original timestamp

    MakeDate(Year(Horodatage), Month(Horodatage), Day(Horodatage)) as Date,

    Num(Hour(Horodatage), '00') & 'h' as Hour,

    Time(Class(Time(Horodatage),0.5/24), 'hh:mm') as Half_Hour,

    Time(Class(Time(Horodatage),0.5/2/24),'hh:mm') as Quarter,

    Time(Class(Time(Horodatage),0.5/6/24),'hh:mm') as Time_By_5

Resident Calendrier_TMP;

But i recently noticed that sometimes rounding is correct and sometimes not (yellow):

Do you have an idea why it doesn't work here? Do you have a better calculation method ?

Thank you by advance !

3 Replies
Gysbert_Wassenaar

Perhaps:

Time(Ceil(frac(Horodatage),1/48)) as Half_Hour

Time(Ceil(frac(Horodatage),1/96)) as Quarter_Hour


talk is cheap, supply exceeds demand
martin_dideriks
Partner
Partner

You could do like this:

Time(Round(MyTime, (1/24/12))) as [5 minutes],

  Time(Round(MyTime, (1/24/4))) as Quarter,

  Time(Round(MyTime, (1/24/2))) as HalfHour

You can use Round,Floor or Ceil depending on your needs.

//Martin

MarcoWedel

LOAD *,

     Time(Floor(Frac(Horodatage),'00:30:00')) as Half_Hour,

     Time(Floor(Frac(Horodatage),'00:15:00')) as Quarter,

     Time(Floor(Frac(Horodatage),'00:05:00')) as Time_By_5

Inline [

Horodatage

02/01/2014 21:18:00

02/01/2014 21:39:00

02/01/2014 21:56:00

];

regards

Marco