Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
nikita23
Contributor III
Contributor III

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

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.

2 Solutions

Accepted Solutions
Vegar
MVP
MVP

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

 

image.png

See attached qvf.

View solution in original post

Vegar
MVP
MVP

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

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

 image.png

View solution in original post

9 Replies
bramkn
Partner - Specialist
Partner - Specialist

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
MVP
MVP

Take a look at my expressions below.

image.png

//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
Contributor III
Contributor III
Author

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
MVP
MVP

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

 

image.png

See attached qvf.

nikita23
Contributor III
Contributor III
Author

Thank you so much Vegar!

 

Its  working fine now. 🙂

Thanks a ton for your help!

nikita23
Contributor III
Contributor III
Author

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
MVP
MVP

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

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

 image.png

nikita23
Contributor III
Contributor III
Author

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
MVP
MVP

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 

 

image.png