Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Highlighted
hykal1993
New Contributor

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

Re: How to group the hour to build a time series chart

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
New Contributor

Re: How to group the hour to build a time series chart

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

MVP & Luminary
MVP & Luminary

Re: How to group the hour to build a time series chart

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
New Contributor

Re: How to group the hour to build a time series chart

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. 

 

MVP & Luminary
MVP & Luminary

Re: How to group the hour to build a time series chart

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
New Contributor III

Re: How to group the hour to build a time series chart

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
New Contributor III

Re: How to group the hour to build a time series chart

You can set the default date format in SET statement .

SET DateFormat='YYYY-MM-DD';

hykal1993
New Contributor

Re: How to group the hour to build a time series chart

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
New Contributor

Re: How to group the hour to build a time series chart

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