topic Re: How to create a Gantt chart using a Pivot table in QlikView

How to create a Gantt chart using a Pivot table

Thu, 06 Mar 2014 14:39:13 GMT

Hi,

I just wanted to share another example on how to create a Gantt chart in QlikView. To do this we can use some of QlikView's available resources as an alternative to the use of Extension Objects. In this case I have used a Pivot Table.

To create this chart I’ve used some example data (Excel file) that can be found on the zip file attached bellow. That file has several fields, enough to identify the tasks of a Project (such as ID, Start, End…).

In order to be able to pick the desired dates a master calendar has been used. Also, the IntervalMatch functionality has proved very useful by generating the dates between the start and end of the different tasks that the project has.

The Hierarchy functionality in the load script generates the different task levels.

There is something peculiar about the data I am using (Excel file). Colors for each task are specified in Hexadecimal format. So during the load script I converted them to RGB colors (color code used by QlikView).

After the data model has been completed a Pivot Table can be used to represent my Gantt chart. To make the chart stable, on the pivot table properties we need to uncheck the "allow pivoting" option. As dimensions we will have each task level, years months and days.

The expression is a bit different. First of all, I it's represented as an Image. That image is a png file (included in the zip file) with transparency that will let us see the background color of each cell.

The expression is as follows:

if(End=Start,'◆',
      if(date(DateID)=date(Today()),'gantt_part_today.png',
           if(Start = DateID and Group = 1,'gantt_part_end_l.png',
                if(End = DateID and Group = 1,'gantt_part_end_r.png',
                     if(End-DateID+1>0,'gantt_part_mid.png',
                          if(date(DateID)=date(Today()),'gantt_part_today.png'))))))

The bacground color for each cell, is already stored in the data. So we just need to use it as a background color for our expression. In this case, if it is not a milestone, use the color value:

if(Mile =0, RGBColor)

That's all. The Pivot table now can be used as Gantt chart. I hope you find this example useful.

Re: How to create a Gantt chart using a Pivot table

Siva_Sankar — Thu, 06 Mar 2014 14:42:08 GMT

omr,

Looks nice. You can create this as document and publish in community, helpful to all of them.

To upload: http://community.qlik.com/document/create.jspa

Regards.

Siva

Re: How to create a Gantt chart using a Pivot table

Tue, 15 Apr 2014 00:05:33 GMT

Awesome. You saved my day!

Re: How to create a Gantt chart using a Pivot table

Tue, 06 May 2014 23:20:03 GMT

I created the chart and everything looks good. But when I view the chart in web view it is getting messed up a bit. Will the same problem exist if the qvw is published to the server and accessed through access point?

Re: How to create a Gantt chart using a Pivot table

Lee_Matthews — Wed, 07 May 2014 03:12:17 GMT

Hey Hose

This is great work. I had developed an extension for this, but this eliminates the need for using that. I will put a link from my posting to here, as this is a great option.

I assume that you could set the level of time granularity as detailed or high level as you wish, just by adding the necessary fields from the master calendar. Is that correct?

Regards

Lee

Re: How to create a Gantt chart using a Pivot table

Fri, 25 Jul 2014 12:20:17 GMT

Thanks!

Yes you could. There is some sample data on the attached file. If you wanted to add more detail to the timeline (such as hour and minutes), that information whould need to be on the source data and on the master calendar.

Re: How to create a Gantt chart using a Pivot table

Mon, 28 Jul 2014 12:51:57 GMT

Hi,

You can also find this post as a documment here: How to create a Gantt chart using a Pivot table

Re: How to create a Gantt chart using a Pivot table

Tue, 07 Oct 2014 14:31:07 GMT

Hi Jose,

When I try to reload it asked for "Field not found Name2" and I can't find out how the name2, name3 come from in the script. Can you help please?

Thanks,

Allan

Re: How to create a Gantt chart using a Pivot table

Tue, 07 Oct 2014 15:22:52 GMT

Hi,

Just to check, did you place the file called ProjectExample.xlsx (included on the zip) next to the QlikView document?

The error you are getting could be because the first table on the load script _tmpProject, does not have valid data.

Please, make sure that the excel file containing the data is reachable by the QlikView App and that the field Name has values.

If you unzip all the contents of the zip file on a folder, the app should reload correctly (as all the paths are relative).

Re: How to create a Gantt chart using a Pivot table

Thu, 09 Oct 2014 12:04:43 GMT

Thanks Jose.

Yes the xlsx is in the same folder, and I find the root cause, I updated the table set all parent 0 as no parent/child relation among tasks in my case, which causing name2 and name3 not valid. I comment out name2 and name3 in the script and it's working great.

Appreciated for your help and the excellent sample.

Allan

Re: How to create a Gantt chart using a Pivot table

Fri, 10 Oct 2014 10:00:38 GMT

You're welcome Allan.

Glad you like the sample!

Re: How to create a Gantt chart using a Pivot table

tripwireqv — Fri, 20 Nov 2015 19:30:53 GMT

I'd like to have this gantt chart identify "Today" and default to being scrolled over such that today is visible (I've a multi-year time span I'd like to display, and the user may need to scroll either direction). Is this possible?

BTW, thanks for the code sample!

Re: How to create a Gantt chart using a Pivot table

Wed, 25 Nov 2015 11:24:10 GMT

Hi Rita,

About the scroll position: There is no way of setting the scroll to a position using the object's properties, there is a partial solution to this using the "Preserve Scroll Position" option of the Layout tab on the object properties. At least this way the scroll position will be maintained while changing tabs or using the app.

About marking today's date, this is already set up on the example (the dates on the excel file are from 2014 so I have tested this changing the dates to more recent ones).

Today's date will be marked using an image (in this case a green circle). The effect should be the following:

I hope this helps!

Re: How to create a Gantt chart using a Pivot table

Thu, 15 Sep 2016 14:28:17 GMT

Is there a reason that there line 2 and the last line are identical? if(date(DateID)=date(Today()),'gantt_part_today.png', Deleting either doesn't seem to have an affect.

Re: How to create a Gantt chart using a Pivot table

Fri, 09 Jun 2017 13:07:58 GMT

Hi Jose,

This is awesome.

How do you get the year, month and day to show on the top row of the pivot table?

Regards

Clint

Re: How to create a Gantt chart using a Pivot table

Mon, 17 Jul 2017 06:55:52 GMT

Hi,

Thank you!

Sorry it did take me a while to see this message. When you create a pivot table, just click and drag a header of a row or column and drop it into a row or a column. You will see a blue arrow while you do this.