Round Time

[#1]

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);

[ecolomer]

You see the file attached

Is this??

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

[#3]

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

[rubenmarin · Accepted solution]

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.

[#5]

Great, thank you Ruben!

[#6]

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

[rubenmarin]

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