3 Replies Latest reply on Aug 8, 2013 4:09 PM by Shawn Wallwork

    Gantt chart and reference lines unique to each record?

    Rachel Alonso

      I have a set of MS Project-originated schedule data for many projects. Each project has 6 dates (and each date has its own column):

      1. Estimated start/end dates
      2. Baseline start/end dates (i.e., targets/goals)
      3. Actual start/end dates (i.e., performance - what I want to measure against the baselines)

       

      I was able to set up a gantt chart with projects on the Rows shelf, and DAY(Design Baseline Start) on the column shelf. The size is based on the # of days duration between the Baseline Finish and Baseline Start Date.

       

      Where I'm running into trouble is adding reference lines for the other dates. I was able to do it for the Baseline Start Date, by setting the value to DAY(Design Baseline Start) -- the same field that determines the axis:

      Ref Line.PNG.png

       

      More importantly than that is to be able to add a reference line for the Actual Finish date, so we can see which projects ended late. I tried creating a parameter based off of the Design Actual Finish field, but then realized parameters can only be displayed for 1 value at a time. I would like the reference line to reflect the Finish date that is unique to each row/record/project. This is what I have in mind:

      Gantt ideal.png

       

      Some of my thoughts for possible solutions include--

      • Is it possible to create a parameter that includes an "All" option, and then base a calculated field off of the parameter? (I'm new to parameters, by the way).
      • A dual axis of some sort, combining my existing gantt chart with lines corresponding to each Actual Finish date
      • Setting the duration to count the # of days between the ACTUAL finish date and the baseline start date - then using some sort of coloring to distinguish the portion of each project's duration that occurred within the baseline dates, and which portion occurred between the baseline and actual finish dates
      • Some sort of a waterfall chart, where each project has two horizontal bars on top of each other- 1 corresponding to the baseline duration (as it is now), and the other showing the actual duration
      • Bullet graph instead of a gantt chart (since we are comparing actuals to goals- but can bullet graphs work with a time-based axis?)
      • Add a separate data source that acts as a master calendar of all dates -- base the axis off of that list of dates, rather than 1 particular date field??
      • Restructure the data using a UNION ALL statement so each project appears in multiple rows

       

      I'm hoping someone who reads this will have a better idea how to achieve this, or at least can tell me which of those ideas would never work I've attached a packaged workbook that contains a set of dummy data for a limited number of projects (FYI-- right now I'm only testing the Design phase, but each project also has another set of 6 dates for 4 other phases! Once I get past this, I'll try to figure out how to deal with the other data as well...)

       

      Thank you in advance for your help!

        • 1. Re: Gantt chart and reference lines unique to each record?
          Shawn Wallwork

          Rachel, this was a fairly long question, which can be good, but also can be a little daunting to digest. Please read this especially #12 and in the future try to zero in on your actual question. The details are good to have but only if it supports the basic question. Here's your question edited.

           

          Rachel Alonso wrote:

           

          I was able to set up a gantt chart with projects. Where I'm running into trouble is adding reference lines for the other dates. I was able to do it for the Baseline Start Date ....  More importantly than that is to be able to add a reference line for the Actual Finish date, so we can see which projects ended late. [Something like this:]

          Gantt ideal.png

          [I've attached a workbook.]

           

          Thank you in advance for your help!

           

          And I've attached a workbook with what I believe is the answer you're looking for. Here's what I did:

           

          1. Drag the [Design_Actual_Finish] pill onto the level of detail shelf
          2. Change it from Year() to Day() the same as your chart's axis.
          3. Added a new reference line. Now you can choose the field as a reference line (because of steps 1 & 2)
          4. Season to taste

           

          Here's what it looks like:

          Gantt.png

           

          Let me know if this isn't what you're looking for. Hope it helps,

           

          --Shawn

          • 2. Re: Gantt chart and reference lines unique to each record?
            Rachel Alonso

            Thank you, Shawn - both for the forum posting tip, and the level of detail solution! Much appreciated.

            • 3. Re: Gantt chart and reference lines unique to each record?
              Shawn Wallwork

              Rachel, you are very welcome (for both). And thanks for taking the time to read the doc a bunch of us put together. We hope it will make for happy forum folks on both sides of the ask/answer equation.

               

              And if no one has done it yet: Welcome to the Forums!

               

              --Shawn