7 Replies Latest reply on Jun 27, 2017 3:33 PM by Alan Lam

    Creating a Gantt Chart with Multiple Dates

    william.boyd.0

      I have done hours of searching on this topic, and I haven't been able to figure it out.

       

      I am trying to create a Gantt Chart that looks like this:

      Sheet 1.jpg

      The problem is, my data is structured like so.

       

      Loan Number
      Application Date
      Appraisal DateSigning DateFunding Date
      Closing Date (Etc.. etc.. etc..)
      11/2/20151/10/20152/5/20152/9/20152/15/2015
      23/16/20163/19/20164/2/2016

      4/8/2016

      4/15/2016

       

      I have around 15 different dates that need to be tracked, so reordering the data with Start and End dates would cause me to have an extremely large amount of records and isn't very feasible.

      Is there any way to show more than 2 dates (by using dual axis)? Is this a limitation that Tableau will never be capable of, or is it just something that hasn't been implemented?

       

      In addition, I need to add Due Dates as shapes on the individual loan rows and cannot figure out how to achieve this, even with restructuring data.

       

      Thank you.

        • 1. Re: Creating a Gantt Chart with Multiple Dates
          Tom W

          Creating gantt charts in Tableau relies on there being a consolidated and shared date axis. I can't think of a way to use multiple dates like this.You could use an alternative like the number of days between steps i.e;

          This would work as the axis is consistently based on a number.

           

          If you wanted to break it up like you wanted, you will need to go down the date pair path;

          Start Date, End Date, Event, Loan Number.

           

          This might not be as difficult or create as much data as you think though. What's your data source?

          1 of 1 people found this helpful
          • 2. Re: Creating a Gantt Chart with Multiple Dates
            william.boyd.0

            Thank you for your reply, Tom.

             

            The view in the image you posted could work if I can include the date of signing, funding, etc. in the tooltip. How did you go about presenting the information that way?

             

            And my data source is an excel sheet. Unfortunately, it contains sensitive information so I cannot post it as an example.

             

            I have written the following SQL code as a test, but it quickly becomes extremely long due to the number of loans and dates there are.

             

            SELECT

                [Sheet1$].[Purpose] AS Purpose,

                [Sheet1$].[Loan Number] AS LoanNumber,

                [Sheet1$].[Borrower Last Name] AS BorrowerLastName,

                [Sheet1$].[Borrower First Name] AS BorrowerFirstName,

                [Sheet1$].[Product Type] AS ProductType,

                [Sheet1$].[SML] AS SML,

                [Sheet1$].[MD] AS MD,

                [Sheet1$].[Processor] AS Processor,

                [Sheet1$].[Lien Type] AS LienType,

                [Sheet1$].[Loan Amount] AS LoanAmount,

                [Sheet1$].[LTV] AS LTV,

                [Sheet1$].[County] AS County,

                [Sheet1$].[Borrower Employer] AS BorrowerEmployer,

                [Sheet1$].[Pre Approval] AS Preapproval,

                [Sheet1$].[Application Date] AS StartDate,

                [Sheet1$].[Appraisal Date] AS EndDate,

                "Application" AS [Phase]

            From [Sheet1$]

            UNION

            SELECT

                [Sheet1$].[Purpose] AS Purpose,

                [Sheet1$].[Loan Number] AS LoanNumber,

                [Sheet1$].[Borrower Last Name] AS BorrowerLastName,

                [Sheet1$].[Borrower First Name] AS BorrowerFirstName,

                [Sheet1$].[Product Type] AS ProductType,

                [Sheet1$].[SML] AS SML,

                [Sheet1$].[MD] AS MD,

                [Sheet1$].[Processor] AS Processor,

                [Sheet1$].[Lien Type] AS LienType,

                [Sheet1$].[Loan Amount] AS LoanAmount,

                [Sheet1$].[LTV] AS LTV,

                [Sheet1$].[County] AS County,

                [Sheet1$].[Borrower Employer] AS BorrowerEmployer,

                [Sheet1$].[Pre Approval] AS Preapproval,

                [Sheet1$].[Appraisal Date] AS StartDate,

                [Sheet1$].[Signing Date] AS EndDate,

                "Appraisal" AS [Phase]

            From [Sheet1$]

            .... and so on and so on for every different date.

             

            This also leaves me at a bit of a loss on how to create Due Date markers that also appear on the Gantt chart.

            • 3. Re: Creating a Gantt Chart with Multiple Dates
              Tom W

              You could create two custom SQL Queries;

              The first to fetch your parent data for the loan, second to select the dates;

              i.e.

              SELECT
                  [Sheet1$].[Purpose] AS Purpose,
                  [Sheet1$].[Loan Number] AS LoanNumber,
                  [Sheet1$].[Borrower Last Name] AS BorrowerLastName,
                  [Sheet1$].[Borrower First Name] AS BorrowerFirstName,
                  [Sheet1$].[Product Type] AS ProductType,
                  [Sheet1$].[SML] AS SML,
                  [Sheet1$].[MD] AS MD,
                  [Sheet1$].[Processor] AS Processor,
                  [Sheet1$].[Lien Type] AS LienType,
                  [Sheet1$].[Loan Amount] AS LoanAmount,
                  [Sheet1$].[LTV] AS LTV,
                  [Sheet1$].[County] AS County,
                  [Sheet1$].[Borrower Employer] AS BorrowerEmployer,
                  [Sheet1$].[Pre Approval] AS Preapproval
              FROM [Sheet1$]
              

               

              Then your second source could be the date union;

               

              SELECT
              [Sheet1$].[Loan Number] as LoanNumber,
              [Sheet1$].[Application Date] as [Start Date],
              [Sheet1$].[Appraisal Date] as [End Date],
              "Application" as Phase
              FROM [Sheet1$]
              UNION ALL
              .......
              

               

              Then join the sources on the Loan Number

               

              This approach will cut down on the SQL somewhat. I know it's still 'long', but after you do it once it's one and done right?

               

              My 'day number' example was achieved by creating a number of calculated fields i.e. AppToAppraisal: datediff('day',[Application Date],[Appraisal Date])

              Create one of those for each phase, drop measure values onto the column shelf, measure names onto the color and label part of the marks bar. You could then go forward and drop the individual dates onto the label or tooltip.

              1 of 1 people found this helpful
              • 4. Re: Creating a Gantt Chart with Multiple Dates
                william.boyd.0

                Thank you for your responses, they have helped immensely. I took the approach of restructuring the data. I now have a second question, however. How would I be able to display due dates? (These are separate from phases. While the signing phase may last 3 months, I could have a "Regulation Due Date" fall anywhere on the timeline.) I was hoping to display symbols that overlaid the graph, maybe using a second axis for DueDates, but I cannot seem to restructure the data further to make this work.

                • 5. Re: Creating a Gantt Chart with Multiple Dates
                  william.boyd.0

                  I solved my problem! I just added two fields called "Due Date" and a "Due Date Type" and overlaid them on the chart. Thanks for all the help!

                  • 6. Re: Creating a Gantt Chart with Multiple Dates
                    Tom W

                    I'm glad I could help out!

                    • 7. Re: Creating a Gantt Chart with Multiple Dates
                      Alan Lam

                      Hi:

                      I came across your example, trying to create the same thing. Would you mind sharing your dummy dataset or share your tableau file? I'd like to make sure I get the DUE DATE and DUE DATE TYPE set up correctly. Thanks!