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. Change the Chart Type to Grid Screen Shot 2022-04-01 at 12.46.10 PM
  4. Drag and drop the following fields into the Grid Data section under Columns
    1. Service Name
    2. Person Name
    3. Provider Name
    4. Service Start Date
    5. Service End Date
  5. 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
  6. 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)

  7. 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
  8. 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"))
  9. 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
  10. 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.