Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Talk to Experts Tuesday, Live Q&A, September 22: Moving from QlikView to Qlik Sense. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
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
Highlighted
Partner
Partner

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.

Plees ekskuse my Swenglish and or Norweglish spelling misstakes

View solution in original post

Highlighted
Partner
Partner

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

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

 image.png

Plees ekskuse my Swenglish and or Norweglish spelling misstakes

View solution in original post

9 Replies
Highlighted
Partner
Partner

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.

Highlighted
Partner
Partner

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

 

 

Plees ekskuse my Swenglish and or Norweglish spelling misstakes
Highlighted
Contributor III
Contributor III

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!

Highlighted
Partner
Partner

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.

Plees ekskuse my Swenglish and or Norweglish spelling misstakes

View solution in original post

Highlighted
Contributor III
Contributor III

Thank you so much Vegar!

 

Its  working fine now. 🙂

Thanks a ton for your help!

Highlighted
Contributor III
Contributor III

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

Highlighted
Partner
Partner

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

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

 image.png

Plees ekskuse my Swenglish and or Norweglish spelling misstakes

View solution in original post

Highlighted
Contributor III
Contributor III

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.

Highlighted
Partner
Partner

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

Plees ekskuse my Swenglish and or Norweglish spelling misstakes