5 Replies Latest reply on Mar 1, 2014 9:44 PM by philmodjunk

    Enrollment Report



      Enrollment Report


           I'm trying to create an Enrollment Report which I can't seem to get a handle on it. I need to know how many clients were actively enrolled into the program by each month with in a Fiscal Year (July-June).

           Here is the report layout I'm trying to create

           Fiscal Year                 January     February   March  April   etc... 

           2012                               ##             ##            ##       ##

           2013                              ##             ##            ##        ##

           In my table I have the EnrollDate and DischargeDate. I realize to generate this report I needed to create some additional fields which is where I'm having a problem in doing. 

           I started by creating several additional fields

           Month 0 -  Formatting the date as mm/yyyy.

           Month 1 -

           Month 2 -

           Month 3 -

           and so on until I reach Month 24

           My Field is setup as a Calculation field.

           Month 0 - Calculation

           Case(IsEmpty (DischargeDate ) ; Month(EnrollDate)&"/"&Year(EnrollDate);
           GetAsDate ( EnrollDate )  ≤ GetAsDate ( DischargeDate ); Month(EnrollDate)&"/"&Year(EnrollDate);
            ; "0")

           Month 1 - Calculation

           Case(IsEmpty (DischargeDate ) ; (Month(EnrollDate)+1)&"/"&Year(EnrollDate);
           GetAsDate ( EnrollDate )  ≤ GetAsDate ( DischargeDate ); (Month(EnrollDate) +1)&"/"&Year(EnrollDate);
            ; "0")

           Month 2 - Calculation

           Case(IsEmpty (DischargeDate ) ; (Month(EnrollDate)+2)&"/"&Year(EnrollDate);
           GetAsDate ( EnrollDate )  ≤ GetAsDate ( DischargeDate ); (Month(EnrollDate) +2)&"/"&Year(EnrollDate);
            ; "0")

           I identified two problems with this.

           1. It will never be false. I need a "0" to appear in the proceeding month fields after the discharge date

           2. Doing the Month + # will continue after month 12 and not start back to month 1

           Meaning if the EnrollDate is 12/13/12 the current output will be 13/2012 instead of 01/2013


           If anyone can help me, I would greatly appreciate it. I can't seem to wrap my head around this and I know it can be done.





        • 1. Re: Enrollment Report

               You have posted this in the section of the forum for FileMaker GO users. Are you using FIleMaker Pro or FileMaker GO to produce this report?

          • 2. Re: Enrollment Report

                 I'm so sorry. I didn't notice that. I'm working in FileMaker Pro.

            • 3. Re: Enrollment Report

                   I started to post an answer for you, but discovered that this would result in my writing a small novel just in response to one post here in the forum.

                   It is possible to get what you want with a "cross tab" type of report, but the calculations, relationships, etc required are greatly complicated by the fact that you have a date range specified in each record and need to count that  record if that date range over laps a specific date range (a month of a fiscal year) for one of the "cells" in your cross tab report.

                   It can be done, but it gets pretty complex to try to describe how to do it.

              • 4. Re: Enrollment Report

                     Thank you for replying back. I figured that it was complicated after spending a couple days working on it before reaching out for help on this website.  I have to get this report created. Would I be able to contact you outside of this website to talk to you about creating this report or would you be able to refer me to someone that I can get to create this report for me. This is a highly critical report that needs to be done as soon as possible. Any help would be great.


                • 5. Re: Enrollment Report

                       If you are interested in hiring me as a consultant, you can send me a private message (click the folder icon that reads "Caulkins Consulting") and we can discuss this. There are several different ways to work with matching a date range in one record to a date range in another. Some evaluate much more quickly than others.