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: 
hykal1993
Contributor II
Contributor II

How to group the hour to build a time series chart

Hi, 

 

My data is as per the image attached. How do I build a line chart where the dimension is hour. Thanks. 

 

Capture.PNG

Labels (1)
9 Replies
Gysbert_Wassenaar

Create an hour field from the Manager Receipt Time field when you load the data : 

LOAD
     ...some fields,
     Hour([Manager Receipt Time]) as Hour,
    ...some other fields...
FROM
    ...source table...

 


talk is cheap, supply exceeds demand
hykal1993
Contributor II
Contributor II
Author

Yes, but the output is blank. 

I believe the format is wrong. How do we change the format to ensure the hour function worksCapture.PNG

Gysbert_Wassenaar

Perhaps your Manager Receipt Time field does not contain numeric timestamps, but string values that first need to be converted into timestamps. You can use the Timestamp#() function for that.

 

LOAD
     ...some fields,
     Hour(TimeStamp#([Manager Receipt Time],'MMM DD YYYY hh:mm:ss')) as Hour,
    ...some other fields...
FROM
    ...source table..


talk is cheap, supply exceeds demand
hykal1993
Contributor II
Contributor II
Author

Thank you, it works. However, this will only give the hours. Our desired final chart on the x-axis would be like below. 

We want to see the hourly trending between the dates. 

Capture.PNG

Appreciate your help, thanks. 

 

Gysbert_Wassenaar

Well, you asked for hours. I've you want to use dates then use dates instead. Change the Hour function to the Date function.


talk is cheap, supply exceeds demand
fallenangel6
Creator
Creator

This  might work.

load 

Date(Date#(ManagerReceiptTime,'MMM DD YYYY hh:mm:ss')) & ' ' & Hour(Date(Date#(ManagerReceiptTime,'MMM DD YYYY hh:mm:ss'))) as datehour

fallenangel6
Creator
Creator

You can set the default date format in SET statement .

SET DateFormat='YYYY-MM-DD';

hykal1993
Contributor II
Contributor II
Author

Thank you. However, now it shows only where the data is available. 

Capture.PNGWe would like to have all the hours showing like in the previous screenshot I shared earlier. 

hykal1993
Contributor II
Contributor II
Author

Capture.PNG

above is example of the chart we built using Excel. You can see the chart will still shows all the hours regardless whether the data exist or not. 

Thanks for your help