4 Replies Latest reply on Apr 15, 2015 11:54 AM by Shawn Wallwork

    How do I show two date ranges in one Viz?

    brian.mcaulay

      I am trying to show two date ranges in one visualization.  One range is when an offer is eligible for booking, and the other range is when the offer is eligible for travel.  In other words, I can book an offer from 1Jul2015 to 15Sep2015 for arrivals (travel) between 15Aug2015 to 1Oct2015.  How would I show the Booking Start and End as a Gantt Bar, with reference lines for the Travel Start and End?

       

      This would be similar to visualizing the planned vs actual date ranges for a project plan.

       

      The data is attached, but I couldn't build a viz to show it.

        • 1. Re: How do I show two date ranges in one Viz?
          brian.mcaulay

          Shawn,

           

          I am on Tableau 8.2.

           

          Thanks for any help you are able to provide.

           

          --Brian

          • 2. Re: How do I show two date ranges in one Viz?
            Shawn Wallwork

            Brian see attached. To recreate, start by using the Excel Legacy connection:

            Legancy.jpg

             

            Make sure to enter the File name before changing to Legacy. Next add the sheet to the connection window and then go to Data menu:

            Convert to Custom.jpg

             

            Then delete that generated code and put this code in:

            SELECT
              ['All-tiers$'].[Objective] AS [Objective],
              ['All-tiers$'].[OfferType] AS [OfferType],
              ['All-tiers$'].[Room_Type] AS [Room_Type],
              ['All-tiers$'].[Travel_end] AS [End Date],
              ['All-tiers$'].[Travel_start] AS [Start Date],
                'Travel' as [Type]
            FROM ['All-tiers$']
            
            UNION
            
            SELECT
              ['All-tiers$'].[Objective] AS [Objective],
              ['All-tiers$'].[OfferType] AS [OfferType],
              ['All-tiers$'].[Room_Type] AS [Room_Type],
              ['All-tiers$'].[Booking_END_DT] AS [End Date],
              ['All-tiers$'].[Booking_START_DT] AS [Start Date],
                'Booking' as [Type]
            FROM ['All-tiers$']
            
            
            

             

            Before clicking OK it's best to preview the results to make sure you don't have any syntax error. (It's usually those pesky little commas at the ends.)

             

            After that I just set up a regular gantt chart. By consolidating the start and end dates we can have both the booking and travel durations on the same line, which is ultimately what I think you want.

             

            Let me know if you have questions.

             

            --Shawn

             

            EDIT: BTW, I learned something new answering this question. I knew we had to have the same field names for the UNION to work properly, but I didn't realize we also had to have them in the exact same order. I guess I'd never run into this before. Thanks for this question.

            • 3. Re: How do I show two date ranges in one Viz?
              brian.mcaulay

              Shawn,

               

              Thanks for the help.  This is exactly what I was looking for.

               

              --Brian

              • 4. Re: How do I show two date ranges in one Viz?
                Shawn Wallwork

                Brian you're welcome! Glad it worked for you.

                 

                --Shawn