topic Draw a time series/time line chart in QlikView

Draw a time series/time line chart

Sun, 25 May 2014 09:46:27 GMT

I am new to Qlikview. I wanted to achieve the kind of plot shown in attachment. This is how i tried doing it.

1. I have the data in excel. Three columns: Date, Start Time, End Time (ex: 06/06/2013 12:15:00 12:30:00)

2. I used line chart, horizontal representation, i.e. Dimension on X-axis is: Date. The expressions are Time([Start Time],'hh:mm:ss')) and Time([End Time],'hh:mm:ss'). The result is shown in attachment "How far i succeeded.jpg". I could put two points where the Start TIme and End Time was, but dont know how to join them.

Here is what i am missing.

1. Join Start Time and End Time using a line. All the events (each event is shown by a bar from Start Time to End Time) in a day will be shown using multiple bars.

2. The horizontal axis is the time axis representing 24 hrs, scale preferably 1 hour.

Thanks in advance.

Re: Draw a time series/time line chart

Sun, 25 May 2014 10:48:08 GMT

I dont think you can do that straight from the data you have. What you have here is Start and End Time as dimension and Date as expression. For the same date, you want to get the trend between your two times - in order to achieve this you should have a single time dimension and have both the times in that column. You have to pivot your time data.

e.g.:

	Date, Time A, Time B
	06/06/2013 , 12:13:00, 12:15:00
	05/31/2014, 13:15:00, 15:!5:00
	01/02/2014, 1:00:00, 5:00:00

Should be

Date, Time

06/06/2013, 12:13:00

06/06/2013, 12:15:00

05/31/2014, 13:15:00

05/31/2014, 15:15:00

01/02/2014, 1:00:00

01/02/2014, 5:00:00

I dont understand the point of this though. Normally the line chart is used to give the trend of something - for example if the sales goes up or down over days ,etc. Not sure what you are trying to achieve here. Sorry couldn't help much .

Re: Draw a time series/time line chart

Sun, 25 May 2014 11:35:33 GMT

Hi Preethi

Thanks for your response. I started with line chart, because i dont know which kind of chart will give the kind of view i was looking for. I am trying to represent how some sequence of operations went on a day. Somehow i wanted to show my data in that kind of view. I can create my data in any format (like the pivot example you said), if that helps to get the view i am looking for. Could you please explain in detail (steps) how this can be done.

Re: Draw a time series/time line chart

Sun, 25 May 2014 12:32:39 GMT

Hi Sandeep,

What you want is a gantt chart.

Check the awesome recipe to accomplish it under those 2 links:

http://community.qlik.com/blogs/qlikviewdesignblog/2012/06/01/recipe-for-gantt-chart

Files for creation of a Gantt chart

Re: Draw a time series/time line chart

MarcoWedel — Sun, 25 May 2014 22:00:59 GMT

Hi,

maybe using error bars is sufficient for your requirements?

regards

Marco

Re: Re: Draw a time series/time line chart

Mon, 26 May 2014 05:19:35 GMT

Hi Marco

Thank you very much. What you have shown really looks like a great and simple solution for what i am looking for. But, I could not open your app (.qvw) file you attached as i am using personal edition and i am getting following error message when i tried opening it (see attachment). Can you send an app that can be opened in the personal edition. Following are the things i could see in your screen shots and I could not replicate the same in my app

1. I want the time axis to look like same as yours (one hour spacing) and all the axis shown in a single screen. (May if i could open your app i could have known how you did that).

2. On the same day there could more than on "Start and End" set like shown in attachment "Value Table". Can you also tell me how this can done.

Thanks in advance.

Re: Re: Draw a time series/time line chart

Mon, 26 May 2014 05:20:06 GMT

Jean-Pierre Bakhache: Thank you for your response. I tried the Gantt chart link that you suggested before posting this question. I could not achieve what i wanted. May be i did not do everything right.

Re: Draw a time series/time line chart

MarcoWedel — Mon, 26 May 2014 10:51:14 GMT

Hi,

I cannot include a licence in my qvw to enable it to be opened with a personal edition.

The time axis in my example is configured like this:

In order to allow multiple time ranges on a single day, I think you would have to create distinct dimension values e.g. like "6/8/2013_1", "6/8/2013_2", "6/8/2013_3" ... either at script level or as a calculated dimension.

regards

Marco

Re: Re: Re: Draw a time series/time line chart

MarcoWedel — Mon, 26 May 2014 15:53:30 GMT

In order to allow multiple time ranges on a single day, I think you could create distinct dimension values with a calculated dimension e.g. like this:

=Date(Date+[Start Time]+[End Time]*0.000001, 'MM/DD/YYYY')

=Dual(Date, Date+[Start Time]+[End Time]*0.000001)

This expression generates distinct dimension values even for multiple time ranges within the same day/date.

The dimension then can be sorted numerically.

The result looks like this:

hope this helps

regards

Marco

Re: Re: Draw a time series/time line chart

Thu, 29 May 2014 03:58:00 GMT

Hi Marco

Thanks for your detailed reply. I liked this idea, but there are multiple rows with same date. Is there a possibility to bring all of them to one row, i mean multiple bars of same date on same line (here bars for same date are marked in multiple lines). If this can be done my problem is completely solved.

Regards,

Sandeep

Re: Draw a time series/time line chart

Sun, 15 May 2016 11:55:46 GMT

Marco, this has worked perfectly for my requirement to display a range between two times on a selected date and for a fixed single dimension. Thanks for your time in posting

Re: Draw a time series/time line chart

Wed, 25 May 2016 12:42:14 GMT

Dear Marco,

I came through this post while searching for a solution similar to this.Is it possible to exclude dates which have both start time and end time equal to zero ?? If so how can we achieve it?Thank you for your time

Regards

Rishi

Re: Draw a time series/time line chart

MarcoWedel — Wed, 25 May 2016 22:07:24 GMT

maybe with a condition like

If(not([start time]=0 and [end time]=0), expression)

regards

Marco

Re: Draw a time series/time line chart

Thu, 26 May 2016 04:45:27 GMT

Dear Marco,

Thank you for quick response.The solution worked well.

Regards

Rishi

Re: Draw a time series/time line chart

peterderrington — Tue, 22 Nov 2016 11:28:00 GMT

Hi Marco,

I'm trying to follow your steps but my chart just shows 'No data to display'

I'm obviously doing something wrong, could you help at all?

Thanks,

Peter