2 Replies Latest reply on Jun 29, 2018 12:48 PM by garth.conrad

    Help to create a timeline with 2 date columns (Start and End Date)

    Fernando Kommers

      Hello!

       

      I need help to create a timeline with 2 date columns. My database is set as the table below:

           

      NameSubjectStatusStart DateEnd Date
      ASubject 1Active02/10/2017
      ASubject 2Active23/11/2018
      ASubject 3Active30/01/2017
      ASubject 2Active27/04/2018
      BSubject 1Active20/03/2018
      BSubject 3Revoked20/05/201720/04/2018
      CSubject 1Revoked20/04/201720/04/2018
      CSubject 2Active04/09/2017
      CSubject 3Active17/02/2018
      DSubject 1Active20/06/2017
      ESubject 1Active25/05/2018
      ESubject 3Active14/08/2017
      ESubject 4Active14/08/2017
      FSubject 1Active07/08/2017
      FSubject 2Active09/06/2017
      FSubject 4Revoked18/08/201720/04/2018

       

       

      This is the graph that I have so far. I was able to count only the ones with "Active" in the Status column, using the "Start Date" dates.

       

      But what I really need is to also add the "Revoked" ones to the graph. For example, "F" should add 1 to the "Subject 4" line at "2018 T3" and then should subtract 1 at "2018 T2".

       

      I'm attaching the workbook (v 10.2.0).

       

      Thanks in advance!

        • 1. Re: Help to create a timeline with 2 date columns (Start and End Date)
          Ken Flerlage

          This is an interesting problem. Ideally, you'd have one record for each time a record goes Active and another when it is Revoked. You can do this with a union. Start out by dragging the table over to itself to union it to itself

           

           

          This will essentially duplicate each record. Next, we'll create some calculated fields. First is a field we'll call "Keep Record" which will allow us to filter out the extra records we don't need.

           

          // Keep all records from the first table and only the revoked records from the second.

          // This will give us 1 record for each active status and another record for each revoked status.

          IF ([Table Name]="timeline_test") or ([Table Name]="timeline_test1" and [Status]="Revoked") THEN

              "Y"

          ELSE

              "N"

          END

           

          Next drag this field to filters and keep only those with a "Y".

           

          Then create a new calculated field which we'll call "Date"

           

          // Give us a single date field.

          // For the first table in the union, give the start date.

          // For the second table in the union, give the end date.

          IF [Table Name]="timeline_test" THEN

              [Start Date]

          ELSE

              [End Date]

          END

           

          This will choose the start date for records from the first table in the union and will choose the end date for the records from the second table, putting them into a single date field.

           

          Next, modify your "Subject Count" calculated field as follows:

           

          // For Active records, add 1. For revoked, subtract 1.

          IF [Table Name]="timeline_test" THEN

              1

          ELSE

              -1

          END

           

          Finally, change the date used on "Columns" to the Date calculated field from above.

           

           

          See attached workbook in 10.2 format. If this helps to resolve your question, please be sure to mark my answer as correct. This will help others with similar questions in the future. Thanks!

          • 2. Re: Help to create a timeline with 2 date columns (Start and End Date)
            garth.conrad

            This same type of question has been asked before.  Here is a great solution: Displaying Active Records Over Time | Tableau Software

             

            Similar types of challenges with dates are listed here:

            FAQ:  Open & Close Dates

             

            G