Tuesday 12 June 2012

Gantt Charts in QlikView

I've been asked more times than I can remember and seen countless posts on the QlikCommunity about how to produce gantt charts in QlikView. As with everything in QV there is more than one way to achieve a gantt chart but I thought I'd give a quick run through the most simple method.

The first thing you need is data, and more importantly data that contains 2 things, a start date and a duration. If you don't have a duration but you have an end date instead, you can calculate the duration by subtracting the latter from the former. For more on dates in QlikView see my earlier post on the subject. Once you have your data structured in this way, putting together the chart is simple. I used the following script to generate some random data for the charts shown in this post. Because the data is randomly generated, if you used this same script to experiment building a gantt chart with, don't expect your charts to show the same exact data.

Data:
LOAD
    TransID,
    chr(ord('A')+mod(TransID,26)) AS Category,
    date(monthstart(today()) + round(10*Rand1)) AS StartDate,
    ceil(10*Rand1) AS Duration;
LOAD
    rand() AS Rand1,
    recno() AS TransID
AUTOGENERATE 50;


Once I ran the this script I had a table of data that looked like this:


You can see I have the 2 key ingredients, a start date and a duration. With the data in place we can start to build our gantt chart. Added a new Chart object and QlikView should bring up its normal chart wizard for you to follow. The chart type we need to use is a bar chart and you may also want to give your chart a useful title before clicking "Next".


The first thing you need to do is add a dimension. This should be the field which contains the values for which you wish to have a bar in your gantt chart for each distinct value. This might be an activity or a project phase for example. In my example data I chose the Category field and clicked "Next".


Next we need to set our expression. The expression will give us the length of the bars displayed and because for a gantt chart we want this to represent the duration of the activity, we use our Duration field within the data. I used the following expression:

sum(Duration)
An important point to note is that I used the sum() function. This is because it may be possible to have more than one entry in my data for each Category and thus I wish to get the total duration of activities for the Category. Don't click on "Next" yet, we have one more thing to set about the expression.


As well as the length of the bar, we need to set the offset of the bars so that they don't all appear to start on the same date. To do this you need to click on the little + to the left of the expression you created do display the available expression properties. Within the "Bar Offset" property we need to define an expression to give us the starting position of each bar. In my example I used the following expression:

min(StartDate)
As before, I used the min() function to return the earliest date if there is more than one activity available for the Category. Once complete you can again click "Next".


You will now be asked to set a sort order for the dimension value should you need to. In my example I am happy with the default alphabetical sort order and so I clicked "Next" again to take me to the Style properties. The key to making my bar chart look like a gantt chart is to set the orientation to horizontal. You can choose to change to look and style of the bars if you wish to but for this example I left the defaults and clicked "Next".


There are a couple of presentation options that can make your gantt chart more user friendly. For example, I chose to limit the number of visible bars to 15 and to provide a scroll bar for users to view the rest should they wish. Don't click on "Next" just yet.


The other presentation option you may wish to add is a reference line showing today's date so that it is easy to identify within the chart. Click to add a new reference line and add the following expression:

=today()
You can change the look and feel of reference line if you wish but I left it standard for this example and clicked "OK".


Once back at the presentation options you can now click on "Next" to take you to the Axes settings. There is one crucial setting we need to change and that is to unselect the "Force 0" option for the expression. If this is selected the chart will start from the QlikView base date of 30th December 1899 rather than a more sensible date based on the data you wish to display. You may also wish to set static minimum and maximum values for the scale and a grid for the expression to make the chart easier to view. Once complete click "Next" again.


You should now be presented with the Colour options. By default your bars will all be the same colour so you can change this here should you wish. For this example I kept the defaults and clicked "Next" again to take me to the Number settings. As your durations are numerical, QlikView will display numbers for the dates along the expression axes by default. We need to change this by selecting the expression and changing the format to Date. If your data is more granular you might want to use the Timestamp format instead to include time. We don't need to worry about any more settings in the wizard so we can click "Finish".


And that's it, you should hopefully have something that looks a little like the example I ended up with shown below. There are many other options to make your gantt chart more user friendly should you wish. For example you might want to include the duration of the bars as text on the data points or even as a popup. Have a play, the options are almost endless.



As I mentioned at the start, there are alternatives to achieve gantt charts in QlikView. For example, you could also use a third party charting tool to produce the gantt chart and embed it inside your document using an extension object. The method above is what I prefer to use and is the simplest to achieve using QlikView's inbuilt functionality.

Happy Gantting!

11 comments:

  1. Hi, I have attempted to build a Gantt chart as an extension object also. Check it out! http://webofwork.com/index.php/19-qlikview-tips-and-tutorials/90-qlikview-gantt-chart

    ReplyDelete
  2. Can you suggest how I would implement your solution following hierarchy:
    OperationDate (Each OperationDate would have multiple entries)
    OperationRoom (Each OperationRoom would have multiple entries per OperationDate)
    PatientID (Each PatientID would have a single entry per OperationDate/OperationRoom)
    Duration (Elapsed minutes From OperationTimeStart thru OperationTimeEnd)

    ReplyDelete
  3. when I use the above logic , x axis shows date in number format. how to change in date format.

    ReplyDelete
  4. Everything works fine, but the bars won't aline correctly to the description in the dimension. Some bars are above, others are too far below the dimension line.. What am I doing wrong?

    ReplyDelete
  5. Go to number tab and check override document settings, then choose date.

    ReplyDelete
    Replies
    1. Hi Hans

      Does that mean you've resolved your issue? I don't think I fully understood what you meant so if its still a problem then let me know and you can send a screen shot.

      Regards
      Matt

      Delete
    2. Yes, by using stacked mode in the Styles tab, all bars now align perfectly. I was using two dimensions, the second was to determine project status.
      The other reply (about number tab etc.) was in reply to what piyush jain asked..

      Delete
    3. Glad you found the solution!

      Delete