Qlik Community

QlikView Documents

Documents for QlikView related information.

How to create a Gantt chart using a Pivot table

Not applicable

How to create a Gantt chart using a Pivot table

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.

Gantt.png

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…).

excel.png

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 background 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.

Attachments
Comments
Not applicable

Great idea. Thanks for posting.

0 Likes
Not applicable

This is exactly what I was looking for! Thank youuu!!!!

0 Likes
hskollo
New Contributor

Very good!! Thank you

0 Likes
evan_kurowski
Valued Contributor

Well done Jose Federico, a well thought out and realized non-extension version of the Gantt.  Don't let anyone tell you you Gantt do it!

0 Likes
ogster1974
Honored Contributor II

Was looking for ideas of how to create a project dashboard.  This has potential.

0 Likes
Partner
Partner

Nice but would use colored Unicode characters instead of images.

Set the text color instead of the background color and you can use characters like █ (U+2588) or ▬ (U+25AC).

There are many Unicode characters available that you can get creative with.

0 Likes
Not applicable

That is a good idea and I did try that at first.

For the milestones I am already using the unicode character :

But I had some trouble with the different fonts as most of the characters did not match in height or width.

0 Likes
ecolomer
Honored Contributor II

Great work,

Thank's for sharing

Saludos,

Enrique

0 Likes
Not applicable

@Jose Federico Moreno Penzo

I don't know if this is a dumb question or not but pls, how did you load the images into your expression?

Thank you

0 Likes
Not applicable

REF:

@Jose Federico Moreno Penzo

I don't know if this is a dumb question or not but pls, how did you load the images into th your expression?

Thank you

Thank you... Figured it out!

Loaded the image inline in the load script.

Then reference it in the Chart Expression

My question now is that, the image will not show up because it's transparent. Please, how do I fix this?

Thank you

0 Likes
Version history
Revision #:
1 of 1
Last update:
‎2014-07-28 08:50 AM
Updated by: