Gantt Chart Help

Thu, 20 Nov 2014 18:24:23 GMT

Hi,

I'm building a Gantt Chart from a horizontal stacked bar instead of using an extension. I've done something similar in MS Excel and plan to use the same approach. The chart is set to show time worked based on timestamp data. Each row of data has a punch in and a punch out. Employees will often more than one punch instance daily (e.g. several punch in/out for breaks). Current chart set up as:

Bar 1: 1st punch data for the day based on timestamp (Bar color set to transparent)
Bar 2: Time duration of the 1st punch data (e.g. 2:29:14) - Bar set to a blue color
Bar 3: Difference between 1st punch data 2nd punch data for the day based on timestamps (Bar color set to transparent)
Bar 4: Time duration of the 2nd punch data Bar set to a blue color

I repeat these steps as many times as necessary based on the number of punch instances by an employee in a day.

My Areas of Need:

In order to sort/order the punch instances in a day by an employee, I had to use the following Previous and Peek formula.

IF([EE Name]=Previous([EE Name]) and Actual_Date=Previous(Actual_Date),RangeSum(Peek('Punch_Count'),1),1) as [Punch_Count]

It seems to work in most cases, but when I load for my entire data table, some individuals do not work properly. I think errors occur when a row of punch data for an employee is not is chronological order by Start timestamp. (e.g. John Doe punch start in row 100 is less than John Doe punch start in row 80; therefore assigning a Punch_Count of '1' to both rows)

Is there a simpler way to give each punch line a unique identifier in chronological order, possibly using a rank based on employee and start timestamp?
- Punch_count is important because Set Analysis is used to call the specific punch for the Gantt chart
How do I get the axis to show as time and start at the date selected?
- I would like to show from 12:00am to 12:00am the next morning.

Attached is the QVW that I'm using.

Any Help is much appreciated.

topic Gantt Chart Help in QlikView

Gantt Chart Help