7 Replies Latest reply on Nov 23, 2010 1:15 AM by Richard Leeke

    Calculated field using a user provided value

    Danny Combs

      I can't figure out how to do this and can't find any advice searching the forums.  Can anyone help?


      I have a table that contains student enrollment data for all of our 32 schools in our school district.  The table has one record for every instance of a student enrolling in a school.  Each record contains the following fields

      Student ID Number

      Enrollment Start Date

      School Name

      Enrollment Exit Date


      So if the student enrolls at one school on Sep 1st, then his family moves across town and he disenrolls and then re-enrolls at the new school on Oct 1st, he will have two records in the table.  The first record will have an enrollment date of Sep 1st and an Exit Date of Sep 30th for school A.  The second record will have an enrollment date of Oct 1st for school B and a null for Exit Date.


      We're trying to depict mobility rates within our school district, school to school.

      I want to display a 32x32 matrix with each school on a row, and each school also in a column.  The row would indicate the number of students enrolled in that school on a date selected by the user, call it the origination date.  The column would indicate the students enrolled in that school on a different date also selected by the user, call it the finish date.  So the user would select an origination date of Sep 15th, and a finish date of Nov 15th, and the viz would display how many students were enrolled at each school on Sep 15th and also where they ended up being enrolled by Nov 15th.  Since 80%+ students don't move the diagonal of the viz would have the bulk of the count.


      Anyway, the only way I can think to do this is to create four fields, two for user input

      origination date

      finish date

      and two calculated fields

      origination school

      finish school

      To get the origination school and finish school for each record I would compare the Enrollment Start Date and the Enrollment Exit Date to the origination date and the finish date and then fill the origination and finish school fields with a lookup of the School Name in that record, or would be null if the origination and/or finish dates are outside the enrollment period in that school.


      Finally, I can't figure out how to create the user input fields for origination date and finish date.  Is there an easy way, or an entirely alternate way to do this?


        • 1. Re: Calculated field using a user provided value
          Joe Mako

          Thank you for all the details.


          Attached is what I came up with.


          - two Date parameter fields (origination/finish)

          - a pair of calc fields to determine if the selected date is between the Start/Exit dates for the student enrollment period, returning the school name if so, and null otherwise

          - a pair of table calcs that return the max school value from the previous function for the student, or "None" if the selected date did not fit for any of the student's enrollment ranges

          - a table calc filter so each student is only counted once

          - a table calc quick filter so you can display one of three options: only cases when a student moved, only cases where students stayed at the same school, or all cases.


          Let me know if you have any questions.

          • 2. Re: Calculated field using a user provided value
            Richard Leeke

            Nice problem.  I couldn't resist having a go at this one, too.  It's a hard problem, but a good exercise in table calculations.


            I did this last night and then compared with Joe's.  Turned out I started out on much the same tack as Joe.  I ran into several issues and so tried coming at it from a few directions.  I've included three of the more successful approaches in the attached workbook, though none of them is completely satisfactory - all give somewhere between incomplete and downright wrong answers.  I've included comments in the workbook explaining what I was doing and where I got stuck.


            At least it gives a few ideas on how to try to represent it.


            Joe: I'm not sure, but I think yours may have the same problem as my first attempt if you give it some more varied data.

            • 3. Re: Calculated field using a user provided value
              Joe Mako

              I don't know Richard :) I think mine is working correctly :) and I believe my data was more varied as I had some students that were in three schools :)


              Attached is my workbook with your data (I added a reverence line to count the marks in each cell)


              One of the things I did intentionally was pay attention to the mark count, there is one mark for each student in the data. This means, a glance at the status bar give you your total count, and you get a tool-tip for each student because each student is a mark. It also enable easy additional drill down with actions, or additional student details can be pulled into the tool tip.

              • 4. Re: Calculated field using a user provided value
                Joe Mako

                I see what you are saying now, you were trying to calculate something that I avoided.


                Your goal was to calculate the total number of students for each situation/cell, and my goal was a mark per student.


                I did not attempt to calculate the total number for exactly the issue you found, you cannot use table calcs for partitioning other table calcs.


                A work around is to use reference line to get the count of marks like I did in my previous attachment.


                One thing I could change is I expected the Exit Date parameter to not be set beyond today. I will fix my calc so that is no longer an issue and repost.

                • 5. Re: Calculated field using a user provided value
                  Joe Mako

                  Richard, in your "Transfers" and "In and Out Flows" sheets, students that either did not start or end in one of the schools were not represented (the N/A ones).


                  And yes, if you do a self join with custom SQL, then all sorts of interesting analysis are possible. I'll see if I can come up with a viz that includes the N/A values and addresses the question in a way like your scatter lines.

                  • 6. Re: Calculated field using a user provided value
                    Richard Leeke

                    Hi Joe, you're quite right, sorry to have doubted you.  I hadn't looked at yours hard enough to see what you were doing differently.


                    That reference line trick is a useful way of getting the count of marks, I didn't think of that when I was trying to do that.


                    Yes, I knew the N/A ones were missing.  I'm sure it's possible to include then with some more tortuous custom SQL, I just decided I'd spent too long on it already.  Will be interested to see what you come up with.


                    So Danny, look at Joe's to see how to do it right or mine if you want to know how to do it wrong.  ;-)

                    • 7. Re: Calculated field using a user provided value
                      Richard Leeke

                      I've done a bit more on this because I can see a very direct application of some of the visualisations I was attempting to some of my own work, so I wanted to see how far I could take it.


                      So with the help of a bit of off-line sharing of ideas with Joe (thanks Joe), here's a slightly refined version.


                      The main difference is that I've adapted it to handle students entering or leaving the district (so not being enrolled in a school at one of the dates of interest).  I've also added a couple of examples showing the effect I was aiming for with setting the line width according to the number of students transferring between each pair of schools.  I didn't manage to do that in the original viz because of hitting table calculation limits (maybe just limits of my own understanding), but copying the underlying data and creating a new data source from the clipboard allows a workaround which demonstrates the visualisation I'm after, which I think is quite effective.  The copy via the clipboard approach of course means that it is now not parameter driven.