6 Replies Latest reply on May 1, 2017 1:23 PM by StylisticGambit

    How do I Automate the Collection of Data with a Calculation

    joshuaw07

      I work with an Academy that has been serving students since 2001. I am combing through the archives and have to determine how many students were actively enrolled each month.

       

      The only data I have is:

      • student name
      • an enrollment date
      • and a discharge date.

       

      Every month in between the enrollment date and discharge date is assumed the student was actively enrolled into the academy.

       

      So in fig.1 below a student was enrolled in january 15th and was discharged at the end of the semester in April.

       

      I need a formula that will automatically return:

      • "Active" every month in between enrollment date and discharge date
      • "Inactive: for every month after discharge date
      • and a "blank" field for every month prior to enrollment

       

      So I in fig1. below, which i manually entered, we see a student that attended our school for the summer semester. He:

      • enrolled on May 15th 2001 (thus each month is blank leading up to May.
      • attended our school in the summer of 2001 (resulting in each summer month being marked active)
      • and discharged in August 15th 2001 ( resulting in every month for the remainder of the year being marked inactive)

       

      Once I can collect this data, I can create a summary field for each month to count how many "active students" as seen in fig. 2

       

      I can collect all this data manually, but i'm sure there is a calculation that can automate this task.

       

      Screen Shot 2017-04-28 at 9.25.17 AM.png fig.1

       

      Screen Shot 2017-04-28 at 9.31.27 AM.png fig.2

        • 1. Re: How do I Automate the Collection of Data with a Calculation
          philmodjunk

          It isn't so much a "formula" that you need but either a query, or a relationship to match to the correct data using inequality operators.

           

          Example, the match fields in your relationship might be something like this:

          Reports::Date >= Enrollment::EnrollmentDate AND
          Reports::Date <= Enrollment::DischargeDate AND

          Reports::StudentName = Enrollment::Name

           

          And it's unfortunate that the only student identifier is a name. Names are not unique and even student names may be changed and this can create issues for your reporting.

          • 2. Re: How do I Automate the Collection of Data with a Calculation
            joshuaw07

            I'm not quite sure I understand how this works. Can you elaborate on this.

            • 3. Re: How do I Automate the Collection of Data with a Calculation
              philmodjunk

              I've also been rethinking this.

               

              Am I correct that you have a single table where each record lists a student a single time with the fields you listed?

               

              And you want to list each month of the current Year and show every month where the student was enrolled on the first day of that month as "enrolled"?

              • 4. Re: How do I Automate the Collection of Data with a Calculation
                joshuaw07

                I have 2 tables, though I may be pursuing this wrong and you may throw out my concept and completely re do it.

                I only need for the end result to be what I described above. How I get there doesn't matter.

                 

                That said here is what i have set up.

                 

                1 which is the student profile with the following fields

                • enrollment date
                • discharge date
                • student name

                 

                The second is a table that represents the year with the following fields

                • each month of the year (so 12 fields)

                 

                I was approaching this so each month field in table two would have a calculation to evaluate the enrollment date and discharge date. The results would be

                • if student A was enrolled that month the month field for that student would return "active"
                • if student B was not enrolled until the following month, it would return a blank field
                • if the student discharged during the month the it would return active until the end of the month.
                • the month after a student's discharge would return "inactive"

                 

                I hope this helps clarify. thanks for your help

                 

                I have some test calucalations that seem to be working to some extent if you want to review some of those.

                 

                Or like I said you can toss out all i've done so far and approach it from another angle.

                 

                Thanks

                • 5. Re: How do I Automate the Collection of Data with a Calculation
                  philmodjunk

                  How I get there doesn't matter.

                  Actually, it does matter. If you get there in a way that makes working with or modifying your system hard to do, it isn't a good approach to use.

                   

                  What you are describing is called a "cross tab" report. A search here will bring up multiple different methods for how you can set them up. None of them are particularly simple to set up. So in addition to any assistance that you get here, you might search this forum using those keywords to find and compare the various methods that you might use.

                   

                  But first, another question: Do you want to select or find a specific student and see their enrollment over a given year or list of years? Or do you want to see this for one year, but for multiple students?

                  • 6. Re: How do I Automate the Collection of Data with a Calculation
                    StylisticGambit

                    For simplicity's sake, I would create a single table, STUDENTS, with these 16 fields.  I think it would do what you want:

                     

                    1.  NAME (Text)

                    2.  DATE_ENROLL (Date)

                    3.  DATE_DISCHARGE (Date)

                    4.  YEAR (Number, Global)

                    5.  JAN (Calculation, Text) = If ( Date ( 2 ; 1 ; YEAR ) - 1 >= DATE_ENROLL ; If ( Date ( 1 ; 1 ; YEAR ) > DATE_DISCHARGE ; "Inactive" ; "Active" ) )

                    ...

                    15.  NOV (Calculation, Text) = If ( Date ( 12 ; 1 ; YEAR ) - 1 >= DATE_ENROLL ; If ( Date ( 11 ; 1 ; YEAR ) > DATE_DISCHARGE ; "Inactive" ; "Active" ) )

                    16.  DEC (Calculation, Text) = If ( Date ( 1 ; 1 ; YEAR + 1 ) - 1 >= DATE_ENROLL ; If ( Date ( 12 ; 1 ; YEAR ) > DATE_DISCHARGE ; "Inactive" ; "Active" ) )