6 Replies Latest reply on Jun 13, 2012 3:52 AM by kym.bell

    Unsure how to set up a motion chart showing changes in enrolment

      I have a spreadsheet with details about individual students, including the 'start date' field (the date they enroll) and a 'date left' field which is blank until they actually leave. The spreadsheet goes back to the beginning of 2008.

       

      I would like to set up a way of taking snapshots at regular (daily?) intervals across the full time of records, so that on any day I can see the number of students enrolled (enrolled on any particular date would be defined as having a 'start date' but a blank 'date left' field). Even better would be a setup so that the changes in enrollment numbers can be viewed on a motion chart.

       

      Any suggestions for how I could proceed? I've done some basic calculated field stuff in Tableau, but not much more.

       

      Kym

        • 1. Re: Unsure how to set up a motion chart showing changes in enrolment
          Tracy Rodgers

          Hi Kym,

           

          I think that a table calculation is probably the best bet.Depending how the view is set up, one recommendation would be the window_sum function. Would you be able to post a sample workbook?

           

          -Tracy

          1 of 1 people found this helpful
          • 2. Re: Unsure how to set up a motion chart showing changes in enrolment
            Jonathan Drummey

            Hi Kym,

             

            It sounds like what you're talking about a queuing calculation, where students enroll or depart and you are looking for a daily/weekly/monthly census and you'd use a running sum. Here's a thread on this from Richard Leeke:

             

            http://community.tableau.com/thread/107815

             

            Jonathan

            1 of 1 people found this helpful
            • 3. Re: Unsure how to set up a motion chart showing changes in enrolment

              Thanks Tracy and Jonathan, I'll investigate your separate suggestions further.

               

              I don't actually have a sample workbook set up, because I'm still unclear about the logic of the best way of going forward.

               

              I've attached a stripped down spreadsheet from our online database, with some of the personal data removed. The 'ED ID' column identifies individual students. The 'Status' column comes from manual entries by our people out in the field, and often shows a mismatch with the existence of a  'Left School' date generated automatically by a separate systems database (ie a student can be shown as 'Active' on our reports but has a 'Left School' date).

               

              I'm interested in those students who have a 'Start Date' and not yet a 'Left School' date on any particular day since 1 Jan 2008.

               

              Kym

              • 4. Re: Unsure how to set up a motion chart showing changes in enrolment

                The thread that Jonathon pointed out seems to have the answer I'm looking for, but now I've hit another brick wall.

                I'm trying to enter custom SQL, but having real problems with establishing a workable 'UNC path' on my computer here at work.

                 

                The spreadsheet I want to work with is in a folder on my hard drive (the D: drive), on my (XP) computer here at work.

                 

                I've plucked the computer name from 'System' in the Control Panel. but can't find the right syntax to use with Tableau.

                 

                Elsewhere on the Tableau site I found that the syntax should be '\\workstation\d$\folder\datasource.xls', but when I attempt to use the address as in between the single quotes, substituting the real computer name and folder path, Tableau will not accept it as the address of the file I want to link to.

                 

                I'm also confused about the syntax in the SQL itself: do I just use the data source file name (having hopefully established the path connection) or does it have to be referenced every time by full path?

                • 5. Re: Unsure how to set up a motion chart showing changes in enrolment
                  Jonathan Drummey

                  Hi Kym,

                   

                  A UNC path is really only required if you are sharing workbooks or uploading to Tableau Server and you don't want to have to deal with making sure that drive letter mappings are the same on different machines.

                   

                  What I usually do when building a Custom SQL data source is connect to the data source normally, and even use the multiple tables option to start building that. Then, when I'm ready to edit the custom SQL, in the Edit Connection dialog I click on the Custom SQL button to have some pre-filled SQL that I can change.

                   

                  Screen Shot 2012-06-13 at 5.41.36 AM.PNG

                   

                  Does that make sense?

                   

                  Jonathan

                  • 6. Re: Unsure how to set up a motion chart showing changes in enrolment

                    Excellent, thanks, Jonathan. That makes sense and is a lot easier than what I was attempting to do.

                     

                    Kym