topic Round Time in QlikView

Round Time

Mon, 11 Aug 2014 14:36:53 GMT

Hi,

I'm trying round my time figures into different intervals, for instance minutes, quarters and half hours.

First I've extracted the time from a timestamp (SettlementTime) using the Num function, not sure if this might be the issue.

I cannot get it to work.

Any ideas?

Thanks in advance,

Olle

SET ThousandSep=',';
SET DecimalSep='.';
SET MoneyThousandSep=',';
SET MoneyDecimalSep='.';
SET MoneyFormat='£#,##0.00;-£#,##0.00';
SET TimeFormat='hh:mm:ss';
SET DateFormat='DD/MM/YYYY';
SET TimestampFormat='DD/MM/YYYY hh:mm[.fff]';
SET MonthNames='Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec';
SET DayNames='Mon;Tue;Wed;Thu;Fri;Sat;Sun';

ColumnHeader:
LOAD
SettlementTime,
Amount,

num(floor(SettlementTime)) as numDateFLOOR,
num(frac(SettlementTime)) as numDateFRAC

FROM

(ooxml, embedded labels, table is Transactions);

Re: Round Time

ecolomer — Mon, 11 Aug 2014 15:07:53 GMT

You see the file attached

Is this??

The value are formatted by DD/MM/YYY and HH:MM:SS

Re: Round Time

Tue, 12 Aug 2014 07:02:15 GMT

Hi Enrique,

I need the numDateFRAC to be rounded to 15 minute intervals, rounding the time to the closest quarter.

12:14:01 should be shown as 12:15

08:04:23 shown as 08:00

Thanks in advance,

Olle

Re: Round Time

rubenmarin — Tue, 12 Aug 2014 07:22:49 GMT

Hi Olle,

You can use Timestamp(round(SettlementTime, (1/96)), 'hh:mm')

or maybe Timestamp(round(Time(SettlementTime), (1/96)), 'hh:mm')

96 are the quarters in a day

Hope it helps.

Re: Round Time

Tue, 12 Aug 2014 07:37:11 GMT

Great, thank you Ruben!

Re: Re: Round Time

Tue, 12 Aug 2014 08:39:39 GMT

Hi again Ruben,

I'm having issues sorting the time intervals when using as dimension in a graph, any ideas?

Guess it has something to do with the format?

Pls see attached.-

Regards,

Olle

Re: Re: Re: Round Time

rubenmarin — Tue, 12 Aug 2014 09:08:13 GMT

Hi Olle,

Timestamp may be shown as the same but as they are from different dates, internally are different numbers, check the sample response I sent and see if this may fit your requirements.

I forced timestamp to convert into text, so internally is the same value for different dates.

Regards,

Rubén