1 Reply Latest reply: Oct 6, 2016 5:13 PM by Sunny Talwar RSS

    Hourly Gantt Chart

    Michael Costner

      Hi everyone,

       

      I'm attempting to create an hourly gantt chart in Qlikview but keep running into a few problems.

       

      My data are structured in three columns:

           id - unique identifier of a particular task

           due_date - date which a task should be completed. Given as a time stamp

           hours - expected number of hours to complete each task

       

      In the script, I'm creating the start date of each task by subtracting hours from due_date. I'm then using three for loops to iterate through each id, date, and hour. My goal is to assign a 1 to each hour of each day that a task should be worked on and a 0 otherwise.

       

      A work day is assumed to run from 10:00am to 6:00pm. For example, if a task is due on 10/9 at 12:00pm and is expected to take 6 hours to complete I want to create the following data table:

       

      DateHourWork?
      10/810:00pm0
      10/811:00pm0
      10/812:00pm0
      10/81:00pm0
      10/82:00pm1
      10/83:00pm1
      10/84:00pm1
      10/85:00pm1
      10/910:00am1
      10/911:00am1
      10/912:00pm0
      10/91:00pm0
      10/92:00pm0
      10/93:00pm0
      10/94:00pm0
      10/95:00pm0

       

      Ideally, once I have this table as output, I will be able to create a pivot table and color each cell based on the 1 or 0 value. The output does not necessarily have to be a pivot table, I just assumed that would be the easiest to create.

       

      My script executes without any errors but I'm having trouble with:

      1. Duplication of data
      2. Creating a logical expression to assign 1 to the hour which a task should be worked on

       

      The due dates in my data set range from a few hours to a few days. When the script executes, it duplicates data by creating a row for each hour of each day for every id in the data set.

       

      I attempted to recreate the script layed out in the blog below.

      How to vizualize a work schedule using a Qlikview Gantt chart | One data at a time

       

      I've attached a sample qvw file with my script and an Excel spreadsheet with a small subset of data and the desired output.

       

      Any suggestions or feedback would be greatly appreciated.

       

      Thanks!

       

      Michael