AnsweredAssumed Answered

Enrollment Report

Question asked by MartaCarriero on Feb 22, 2014
Latest reply on Mar 1, 2014 by philmodjunk

Title

Enrollment Report

Post

     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.

      

      

      

      

Outcomes