Custom Reports
      Back to home
      1. Knowledge Base
      2. Reporting
      3. Custom Reports

      Point in Time Reporting: Which clients did I serve on this day?

      This article will cover how to use Calculated Fields to create a report that shows clients enrolled in a service on a specified day.

      How to create a report that answers the question: Which clients received a service on this day? 

      1. Create a New Visualization 
      2. Select the Service_Enrollments Dataset 
      3. Drag and drop the following fields into the Grid Data section under Rows
        1. Service Name
        2. Person Name
        3. Provider Name
        4. Service Start Date
        5. Service End Date
      4. Add a new Calculated Field by selecting the + Icon in the Fields List, then selecting Calculated Field
        Screen Shot 2022-04-01 at 12.53.20 PM
      5. Name the field "Date Field", then add the following formula. You are able to change this date to any date that you wish to report on. In this example, we are wanting to report on April 14, 2022.  

        1. date(2022,04,14)

      6. Add a another new Calculated Field by selecting the + Icon in the Fields List, then selecting Calculated Field
        Screen Shot 2022-04-01 at 12.53.20 PM
      7. Name the field "Point in Time", then add the following formula: 
        1. if(isempty([service_end_date]), if(and([service_start_date] < [Date Field] + 1,(today() > [Date Field] - 1)),"True","False"), if(and([service_start_date] < [Date Field] + 1,([service_end_date] > [Date Field] - 1)),"True","False"))
      8. Add your new "Point in Time" field as a visualization filter, and select to filter by "True"Screen Shot 2022-05-16 at 3.32.06 PM
      9. Save the report by selecting the checkmark icon in the upper right-hand corner. 

      Note: You can adjust the date that you wish to filter by at any point in time. To do so, select the three ellipses on the calculated field, Date Field, then select Edit. You can then change the date. 

       

      Some other Point In Time Calculated Fields: 

      Scenarios:
      1. Started in the Reporting Period
        1. AND(Start Date >= [Start of Reporting Period], Start Date <= [End of Reporting Period])
      2. Ended in the Reporting Period
        1. AND(End Date >= [Start of Reporting Period], End Date <= [End of Reporting Period)
      3. Started before beginning of the Reporting Period & Ended after the reporting period
        1. AND(Start Date < [Start of Reporting Period], End Date > [End of Reporting Period])
      4. Started before beginning of the Reporting Period, No End Date
        1. AND(ISBLANK(End Date), Start Date <= [Start of Reporting Period])

      Note: You will need to create calculated fields for 'Start of Reporting Period' and 'End of Reporting Period'. To do so, determine what these dates will be for your reporting needs, and create a calculated field containing a date function: date(). 

      So, depending on how you enter your data you'll need to evaluate if the above scenarios cover all potential options for what you define as a service enrollment being offered in a point in time.
      From here, you can choose as many of the above scenarios as you want and combine them into a formula IF(OR([insert all the and statements you want here]),"In Reporting Period", "Not in Reporting Period").
       
      An example of all of them put together would be:
      IF(OR(AND(Start Date >= [Start of Reporting Period], Start Date <= [End of Reporting Period]), AND(End Date >= [Start of Reporting Period], End Date <= [End of Reporting Period), AND(Start Date < [Start of Reporting Period], End Date > [End of Reporting Period]), AND(ISBLANK(End Date), Start Date <= [Start of Reporting Period])), "In Reporting Period", "Not in Reporting Period")
       

      Note: < means before and > mean after