To show every 15 minutes in dimension time format hour:minutes:seconds

[nikita23]

Hi All,

 I have a date/time field in my table.

Records of that field are like as below

2019-05-03 08:34:25,

2019-05-03 09:54:15,

2019-05-03 20:44:20 etc.

Currently, I am using  time(round( frac(trim(df_timestamp) + (12/1440) ), 1/24/4) , 'hh:mm') , which is giving the value like 9:00 AM, 10:00AM, 8:45 PM

now the problem is whenever  the time is like as below means if seconds are above 30 it will goes into another incremental time  bucket of 15 minutes

2019-05-03 20:44:36 it comes under the 9:00PM

Please suggest some solution.

[bramkn]

The function Class() can be used for buckets. (of course seperate the date and time first) If you just want to show the first or last value of the bucket you can use subfield to get that value.

[Vegar]

Take a look at my expressions below.

//Using floor
= time(floor( frac(df_timestamp ), 1/24/4) , 'hh:mm') 

//Using ceil
= time(ceil( frac(df_timestamp ), 1/24/4) , 'hh:mm') 

[nikita23]

Hi Vegar,

Thank you for your inputs but its not working fine in our scenario.

Please find the attached Sample input file for timestamp. It contains Data in sheet 1, issue in sheet2 along with the required output.

Many thanks in advance!

[Vegar · Accepted solution]

If you add a very small number  (eg. pow(10,-16), your timestamp you can achieve what you want by my previous expression using ceil.

time( 
  ceil( frac(TimeStamp) + pow(10,-16),  1/24/4) 
, 'hh:mm')

See attached qvf.

[nikita23]

Thank you so much Vegar!

Its  working fine now. 🙂

Thanks a ton for your help!

[nikita23]

Hi Vegar,

I am not able to sort the time stamp dimension for e.g its showing 00:00 in the last.

Could you please suggest some solution for this so that I can fix the range for time stamp in bar chart.

Many thanks in advance!!

[Vegar · Accepted solution]

Skew the time by a minute in the sort order, like this:

=time(frac([15min calculated]+time#(1,'m')))

 

[nikita23]

Thank you so much Vegar.

Working perfectly fine in my scenario.

I have one more query regarding the same I just need to display all the 15 min buckets in bar chart whether they do not have any data on that particular time bucket in data but want to displaying it in x-axis.

[Vegar]

If you do this script then you can get all dimensions in your presentation:

[Data$]:
LOAD
  [Code],
  Timestamp(Timestamp#([TimeStamp], 'MM.DD.YYYY hh:mm:ss') ) AS [TimeStamp],
  [Count(code)],
  [TimeStamp1] AS [15min desired],
  ceil( frac((Timestamp(Timestamp#([TimeStamp], 'MM.DD.YYYY hh:mm:ss') ))), 1/96)  AS [%15min]
FROM 
  [lib://Downloads/Sample Input for Timestamp (2).xls]
  (biff, embedded labels, header is 1 lines, table is Data$);

FOR i=0 to 95 
  Dimension: 
  LOAD 
    ceil( $(i)/96, 1/96)  as [%15min],
    time(ceil( $(i)/96 +pow(10,-16), 1/96),'hh:mm')  as [15min calculated]
  AutoGenerate 1;
next