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.