11 Replies Latest reply on Jul 23, 2018 8:40 AM by Don Wise

    Best way to turn a series of dates into a single timeline?

    doug funny

      Hi all, I'm new to the data viz world, so this may be a dumb question.

       

      I have a series of continuous dates (as in March 1978 - June 1981) that I'd like to gather together into a single timeline.

       

      For example, let's say I was looking at guests staying at a motel in June, and the input data looked like this:

       

      Guest NameStay Began on...Stay Ended on...
      Guest 1June 3June 15
      Guest 2June 9June 10
      Guest 3June 9June 19
      Guest 4June 22June 30
      Guest 5June 29June 30

       

      I'm looking for a timeline of June, that just tracks the TOTAL number of guests on a particular day (x axis = date, y axis = number of guests in motel)

       

      So the graph would look something like this:

       

      Screen Shot 2018-07-20 at 2.29.55 PM.png

       

      What's the best way to accomplish this in Tableau?

       

      Bonus question: let's say the guestbook included which country the guests were visiting from. Could I color-code those countries into the graph? Like if Guest 3 were from Spain, could I have part of the blue bar be Red for June 9 - 19? If this complicates things too much, no worries!

       

      Thanks ahead of time for your help!

        • 1. Re: Best way to turn a series of dates into a single timeline?
          Mahfooj Khan

          Hi,

           

          You need to transpose your date from columns to rows so that you can get all the date values in one field. There are several ways to transpose your data in tableau. I don't know which data source you're using. If you're using excel source then you can use Pivot option to transpose your data. See the screen shot. Select your date fields and choose pivot option from the down arrow.

          You'll get this view

          Now go to worksheet and create your viz. Use count/countd() function to get the count of guests

          Hope this will help. Sample workbook v10.5 attached for your reference,

           

          Mahfooj

          • 2. Re: Best way to turn a series of dates into a single timeline?
            doug funny

            This helps a lot!!

             

            One issue: Your method puts a mark on the date where the guest's stay begins, and one where it ends, but nothing in between.

             

            For example, Guest 1, from Spain, shows a red mark at 2003, and one at 2015. But there are no red marks in between.

             

            Can Tableau recognize Start Dates & End Dates and interpolate the data in between?

            • 3. Re: Best way to turn a series of dates into a single timeline?
              Don Wise

              Hello Doug,

               

              Please see my recommendation, which uses this Tableau reference ( How to display a daily number of guests based on the start and end dates of their stays at my hotel ?) supplied by kettan for us of a Cartesian join (1:1) between Reservation Dates and Contiguous Dates (fact table).  A filter then checks to see whether the reservation dates fall within the contiguous dates (T|F).  Filtered to True.  Number of records found are then displayed by date.

               

              Both MS-Excel worksheets and the workbook (2018.1 version) are attached as reference.  Screenshot below.  If this resolves your question, please mark this response as correct so that others may find it useful.  Thanks!  Don

               

              Screen Shot 2018-07-20 at 5.13.15 PM.png

              • 4. Re: Best way to turn a series of dates into a single timeline?
                doug funny

                Wow, what an incredibly relevant discussion to link to!! I swear I just used the motel situation as an example — my project has a completely different focus (about politicians in office) — so that coincidence is extra wild for me!

                 

                Anyway, I tried out the method, and it mostly worked great!

                 

                The main issue is that there are several dates with NO entries, and the graph seems to skip them over — i.e. let's say there was nobody staying from June 5-8... the dates along the x-axis would read: June 1, 2, 3, 4, 9, 10, 11 etc...

                 

                In other words, the gap from June 5-8 isn't visually represented. What's the best way around that?

                 

                And I got the color-by-location to work, but I have a new challenge:

                 

                Would it be possible to simultaneously show a line denoting the total number of rooms AVAILABLE? the hotel example doesn't really make sense any more, but basically as if the hotel had 10 rooms at the beginning of June, then expanded to 15 rooms on June 8, then 20 rooms on June 15, and finally 30 rooms on June 28.

                 

                Could we somehow visually track the number of rooms available?

                 

                Thanks so much for your continued help!!

                • 5. Re: Best way to turn a series of dates into a single timeline?
                  Don Wise

                  Hello Doug,

                  Yes, both requests are feasible.  Please see the newly attached workbook.  I modified the original MS-Excel data to exclude the dates you requested.  As seen below, the dates remain contiguous however because there is no data, there will not be a graphical representation other than the contiguous dates.

                   

                  The below also shows the calculation which hardcodes the number of rooms available by Date span as requested, however, only if there are guests.  If there are no guests, then the date is still returned on the axis to show continuity to the next available date where there are guests and rooms.

                   

                  Another Way shows how it could be done using the new Step Line feature in Tableau, which might more closely represent what you're trying to achieve?

                   

                  This solution involves using the dual-axis feature to overlay one chart onto another chart with the same type of x-axis, then synchronizing the y-values axis.  If this resolves your question, please mark this response as correct so that others may find it useful in the future.  Thx, Don

                  Screen Shot 2018-07-22 at 9.37.27 AM.png

                  Screen Shot 2018-07-22 at 9.55.37 AM.png

                  • 6. Re: Best way to turn a series of dates into a single timeline?
                    doug funny

                    Awesome!! Getting so close --

                     

                    Thank you for making workbooks for this little problem! Two questions:

                     

                    1) I still don't understand how you got Tableau to add spaces for those null dates. When I try to recreate this with my dataset (which involves years, not days), I get an x-axis like this:

                     

                    Screen Shot 2018-07-22 at 2.30.58 PM.png

                     

                    As you can see, it skips 1872-1875, and 1882-1967. I would like it if there were just empty spaces there, like in your workbook. How did you accomplish that?

                     

                    2) Is there a way to do the secondary graph ("total number of rooms available") without hardcoding it? By integrating another excel spreadsheet, perhaps? My secondary dataset has a couple dozen changes through the timeline, and I'd rather not retype it all as code...

                     

                    Thank you again!

                    • 7. Re: Best way to turn a series of dates into a single timeline?
                      Don Wise

                      Hi Doug,

                      The reason the date axis is continuous is due to the Cartesian (1:1) join that I did between two different MS-Excel worksheets.  Worksheet #1 simulated the reservations (Guest Name, Date Arrived, Date Left).  Because the date data is intermittent, meaning there are gaps in it like your YEARS data, then we need to attach another range of contiguous dates which is in Worksheet #2. 

                       

                      So, if you were to join the worksheet you have in your example with a new Excel worksheet which is a simple column of contiguous years using the cartesian join method shown in the screenshot below, then you should be able to achieve the effect that was in the last example workbook I provided.

                       

                      Edit Join.png

                      Join Calculation.png

                      Result.png

                       

                      The calculation for number of rooms available across dates is:

                       

                      IF ATTR([Dates])>=DATE('2018-06-01') AND ATTR([Dates])<=DATE('2018-06-07') THEN 10

                      ELSEIF ATTR([Dates])>=DATE('2018-06-08') AND ATTR([Dates])<=DATE('2018-06-14') THEN 15

                      ELSEIF ATTR([Dates])>=DATE('2018-06-15') AND ATTR([Dates])<=DATE('2018-06-27') THEN 20

                      ELSEIF ATTR([Dates])>=DATE('2018-06-28') AND ATTR([Dates])<=DATE('2018-06-30') THEN 30

                      END

                       

                      It's just a matter of adding and deleting what's above by copy and pasting.  There might be another way of doing what you're trying to achieve, but so far I don't have any relevant data to work with and I would be guessing and I'd probably fail.  So unless you'd like to supply your workbook, there's not much more I can do aside from the examples already provided.  Thx, Don

                      • 8. Re: Best way to turn a series of dates into a single timeline?
                        doug funny

                        Ah, sorry my example made things more complicated instead of simpler — I should've just shared my project from the beginning.

                         

                        Attached is my workbork. The project is looking at the number of African American members of US Congress over time. One chart for the Senate, one for the House. I'm working on the Senate first, as a test run, since that list is a lot shorter (10 senators, vs 140 reps)

                         

                        Thank you taking the time to explain the 1:1 join, but I do understand that part (thanks to your previous posts). I had successfully done the join. I'm not sure the difference between continuous dates and contiguous dates in this instance, but my Lookup sheet contained every single year between 1870 and 2018. In my Workbook, you'll see the Data Source page shows entries for the years 1882-1967 (when there were no black Senators)... but still, in the visualization, those years are not shown.

                         

                        As for the Secondary Line, typing out the hard code for the Senate wasn't too bad. I'd still like to know if there's an easier way — since the House has changed size about 20 times (since 1870), and the more data is hand-copied, the more it's prone to error.

                         

                        But if hard-coding is the best way to go, I don't mind putting in the time!

                         

                        I'm sorry for adding extra confusion to all of this, and you will definitely get the "Correct Answer" credit on this one!

                        • 9. Re: Best way to turn a series of dates into a single timeline?
                          Don Wise

                          Hi Doug,

                           

                          Only a couple of minor things needed to be synced up.  I changed the filter calc to match on year of the date-part value for year and changed Year(Years) in the columns shelf from Discrete to Continuous and that brought in your contiguous years.  I also changed the axis to show more years as much as possible, it may not look like it due to the time span, but they're all there.  Ideally, the number of Senate Seats would be in a separate table to make things easier instead of doing a calc. as simply a separate column of data by year and number of seats available.  I reattached your workbook...

                           

                          Hope that helps!  Thx, Don

                          Screen Shot 2018-07-22 at 8.25.44 PM.png

                          • 10. Re: Best way to turn a series of dates into a single timeline?
                            doug funny

                            Incredible! This is so very very close!

                             

                            I only see one issue: The Secondary Graph (Total Senate Seats) looks wrong during the "gap years" (e.g. 1881-1967).

                             

                            You see that big slope in the middle? That's not what the hard-coded data would make it look like... it shouldn't even have any slope, it should always be steps upwards...

                             

                            You suggested I make a separate table with the available Senate Seats on it... could I do that in the "Lookup" spreadsheet? Or would that mess up the Cartesian join? What's the best way to do that?

                             

                            Thanks!

                            • 11. Re: Best way to turn a series of dates into a single timeline?
                              Don Wise

                              Hi Doug,

                              So in your workbook, I didn't change the formatting settings of the work that had already been done other than the years axis to bring more of the years into the view.  It's easy enough to change that background chart:

                              Change from Area to Line.png

                              Select Line Type from Path.png

                              Step Line Approach.png

                              Jump Step Line.png

                              And yes to your last question about adding an additional sheet and doing an additional Cartesian Join.  Please see newly attached workbook (V2) which shows the join.  All of the above screenshots are from your original version. In V2, some newly calculated fields needed to be done.  Lastly, the Number of Senate Seats will show as a Measure because it is a number.  Therefore the number of Senate Seats won't display correctly. The trick to displaying it correctly in the view is to change it to a Dimension once it is dragged onto Rows.  Below is how it was done in Excel for the spans of time.

                              Screen Shot 2018-07-23 at 8.39.30 AM.png

                               

                              Hope that helps!  Thx, Don