1 2 Previous Next 25 Replies Latest reply on Feb 20, 2013 10:41 AM by Shawn Wallwork

    How do I create a Date field to show a timeline of events?

    Danny Combs

      I want to track the enrollment of a school/class over time and display it with a line chart.  My data set has three fields, Student Number, Start Date (the date the student enrolled), and End Date (the date the student withdrew).  If the End Date is blank it means the student is still enrolled.  I know how to display a count of the students and display it has a Running Total line chart on Start Date (see attached).  But I need to show net enrollment whereby the count is reduced when a record gets to its End Date.  Any suggestions?

      Thanks,

      Danny

        • 1. Re: How do I create a Date field to show a timeline of events?
          Richard Leeke

          The best way (in fact the only way I can think of) is to restructure your data to show enrolment and withdrawal as two types of events against a common date dimension. That way enrolment causes a change of +1 and withdrawal causes a change of -1 in the total number enrolled and you can just work out the running sum of the changes.

           

          You can either physically restructure your source data or use custom SQL to rearrange it.

           

          There have been lots of postings on variants of this problem over the years. Have a look through the results of this search for "queue depth" and you should get all the pointers you need.

           

          Alex Kerin also wrote up an excellent summary of a lot of the ideas from those threads on his blog.

          • 2. Re: How do I create a Date field to show a timeline of events?
            Shawn Wallwork

            A note to Newbie Custom SQL writers (me) when you get the syntax wrong Tableau will give you this message:

            ERROR.png

             

            Ignore the part about "access privileges" and "open in another application", it has nothing to do with these things. You've got some bad syntax. (In my case it was a missing parentheses.)

             

            --Shawn

            • 3. Re: How do I create a Date field to show a timeline of events?
              Shawn Wallwork

              Danny, I followed Richard's suggestion, and used Alex's Custom SQL technique to create the attached workbook. The chart looks like this:

               

              Step Chart.png

              At the bottom of Alex's blog, Joe links to a different technique to accomplish the same thing. Since Alex's way doesn't require writing any calculations, and the SQL is fairly easy to write, Joe I'm wondering why you feel it is "less than ideal"? What are the merits of your method, that recommend it over this method? Thanks,

               

              --Shawn

              • 4. Re: How do I create a Date field to show a timeline of events?
                Jonathan Drummey

                Hi Shawn,

                 

                A clarification here: The Tableau part is "An error occurred while loading the data." The rest of the message is all the Microsoft JET driver and it's horribly confusing because it's not telling you which FROM clause (there could be several) or flagging where the error occurred.

                 

                One other common error is the "No value given for one or more required parameters."

                screenshot1.gif

                 

                In this case, the error is that there's a table name or field name - everything that's between square brackets in the Custom SQL, like [Orders$].[City] that doesn't exist in the data. So for example, if you're typing along and type [Orders$].[Citi], you'll get that errror.

                 

                For this reason, I do as much as possible in Custom SQL via copy & paste.

                 

                Jonathan

                • 5. Re: How do I create a Date field to show a timeline of events?
                  Shawn Wallwork

                  Jonathan thanks for the additional info. I broke my own rule, always writing code in Notepad++, which is how/why I got the mismatched parentheses. Agree with you on copy/paste, SQL is super syntax sensitive, especially commas and periods.

                   

                  --Shawn

                  • 6. Re: How do I create a Date field to show a timeline of events?
                    Alex Kerin

                    I like Joe's method of using the path shelf for a stepped chart. However I was not able to take his example on the thread mentioned and make it work in my situation with the adding/decrementing.

                    • 7. Re: How do I create a Date field to show a timeline of events?
                      Shawn Wallwork

                      Hey Alex, when's Part II to your blog coming out?

                       

                      --Shawn

                      • 8. Re: How do I create a Date field to show a timeline of events?
                        Joe Mako

                        Shawn, Richard exlains the reason why using the path shelf is the more ideal route:

                        That approach of using the path shelf to force the order of points in the line is very good Joe - much better than the way I did it in that old thread you pointed to where I staggered the actual date-times by a small amount.

                        Alex's and Richard's original route are good enough in my opinion, a good approxamation of a steped line chart, but if you were to zoom in, you would see that the vertical lines are not exactly vertical, as they are when using the path shelf.

                         

                        Yes, it is a minor detail, but I love details!

                         

                        Alex Kerin, I saw your comment that you were not able to make this work in your situation, can you please provide a sample packaged workbook where the path shelf is not working for you, and I would be glad to show you an approach that can work.

                        • 9. Re: How do I create a Date field to show a timeline of events?
                          Shawn Wallwork

                          Thanks Joe, makes sense. I was just trying to work out your method using Alex's data. I've attached an Excel file with his data on the "original" tab. One thing I'm seeing is that in your original post you have simply doubled your data source in your SQL, where as Alex (and Danny) have two different fields for the Open & Closed dates, which is probably where the difficulty lies.

                           

                          I'll be interested to see how you accommodate this difference.

                           

                          --Shawn

                          • 10. Re: How do I create a Date field to show a timeline of events?
                            Richard Leeke

                            You need the custom SQL to use start date for one of the copies and end date for the other -calling them both the same thing.

                             

                            Take a look at the very original queue depth thread of Alex Blakemore's -his data was structured just like this and that thread explains it well,, I think.

                            • 11. Re: How do I create a Date field to show a timeline of events?
                              Joe Mako

                              Because this example needs a table calc, a slightly different approach is needed.

                               

                              When I commented on Alex's blog back in Oct 2012, I did not attempt it with his data, but I feel the overall concept is the same. Have two marks for each date, sort those marks with the Path shelf so Tableau plays connect the dots the way you want, and them compute your table calc.

                               

                              Please understand that no solution in Tableau can be fully generalized. If I ever post a route, and then you attempt to apply it your situation, and it does not work, please let me know, I am sure there is an adjustment that can make it work.

                               

                              I believe Shawn posted a comment the other day about attempting solutions that you recommend, and I failed to do that back in Oct. I am sorry Alex. I hope we can talk one day, and work things out.

                               

                              Please let me know if you have any questions about the attached, it does require awareness of table calc details, and this is a route custom built for this exact situation, and any variation to computation requirements, may require a different route, but still use the same overall concept.

                              • 12. Re: How do I create a Date field to show a timeline of events?
                                Shawn Wallwork

                                Hi Joe thanks for posting that example. But I was hoping you'd show how to use your path shelf technique using the un-reshaped data on the 'original' tab. One of the points of Alex's blog was that if your data isn't in Excel then the reshaper tool isn't available to you, so the reshaping needs to be done in the SQL. If you have time I'd sure be interested to see how to do this.

                                 

                                --Shawn

                                • 13. Re: How do I create a Date field to show a timeline of events?
                                  Joe Mako

                                  One of the key things about Tableau is that if you want to draw a mark in Tableau, in this case a vertex on a line, then the Visual Interrupter needs a record for each mark you want Tableau to draw. This is one of the key steps in making Tableau produce any result you want.

                                   

                                  So if we want two vertexes for each date, at a min, we need two records for each date of interest. There are a great number of ways to generate more records for the Visual Interrupter. The attached show just some options, there are many more ways to get more marks in Tableau.

                                   

                                  One way to make more records is to use custom SQL, and that can be a good option in some situations, but it can have downsides as well if connected live and based on the exact situation. in the attached is an example with custom SQL and the path self instead of shifting the date by one second. It is the same formulas as the reshaped one, but with a filter on Date to remove the Null values.

                                   

                                  Another option if you do not want to use custom SQL is to use a data blend with a scaffold data source, as in the attached. I use Show Missing Values to perform domain padding. This may also not be an ideal route for everyone because it requires a great deal of knowledge about Tableau. The calculations are also a little more complex, but it is the same general concept.

                                  • 14. Re: How do I create a Date field to show a timeline of events?
                                    Shawn Wallwork

                                    Thanks Joe. Interesting stuff. And thanks for the scaffold version. Let me digest it, I'll get back with questions.

                                     

                                    --Shawn

                                    1 2 Previous Next