This will guide you through the process of finding how many new clients were served per quarter for a reporting year as well as the total number of new clients. You may use this method to report by month or any other time period your organization may use.
Introduction
This article will outline a scenario where we have just begun the year 2019. Now, since 2018 is all finished we want to figure out how many new clients we received per quarter for the calendar year of 2018 as well as the total of new clients.
Please note, this is different than total clients served. A "new client" is described as anyone who enrolled into a program beginning the 1st quarter of 2018 or later. To be considered "new" in quarter 2, 3, or 4 they could not have been considered new in any of the quarters prior. This means, if they had a Program that began in December 2017 and did not enroll in any programs after that, they would not be considered for this report, since they were new as of December 2017.
Global Values
We will want to use 2 Global Values: the "Start Date of Year" and the "End Date of Year". This is because we will need to filter down the beginning of the period to the very end of the period in every section. We want to do this because we will want to look at all of the records in that period. These should be "locked" because these should not be touched and will need to remain the same in every section.
Section Setup
All sections will have the same configuration with the exception of the filters. Because of this we will create just one section and then make 3 copies of it and adjust the filters as we go along.
We will bring in the Record ID from the Household form to ensure this is the root form.
We will bring identifying information from the Individual form such as First, Last Name and Record ID.
We will then bring in the Start Date, End Date and Program from the Enrollment/Exit form.
We will go into the Section Properties and change the Display Style to Totals (this is optional) and then we will also change the Count to Individual Records, and name it "1st Quarter".
We will then add our Filter of "Start Date is Between" [Start Date of Year] and [End Date of Year]. Your section should look like the screenshot below:
Next, expand the Special Columns area from the Field Choices palette and pull the Text Calc column into the report section.
Click the gear for the Text Calc column and we will do the following:
Name it Client Full Name.
Group By This Column = Yes.
Click the "+" button until there are 5 total dropdown boxes in the properties of the Text Calc column.
Within five dropdown boxes in the Calculation Columns, enter the following text:
"First"
" " (just a space)
"Last"
"-" (a hyphen)
"Client Record ID"
Next, we will want to click on the gear icon for the Start Date column.
For the "Summary" we want to choose "Earliest".
Finally, we will create a "Group Filter". Group Filters filter on the "Summary" information. So, for example, since we are looking for the "Earliest" start date, we want to ensure that we are only looking for the 1st quarter. That means our Group Filter will be between 01/01/2018 and 03/31/2018.
Reminder, we still need the regular Filter to ensure we are only looking at records for 2018. Apricot's order of operations is (1) regular Filters and then (2) Group Filters.
That's it! We now have our first working section to see new clients in the 1st Quarter of 2018. Next, we will go over how to copy/adjust that section and then create a "Totals" section to show a quick overview of our results in just one section versus having to scroll through the entire report.
Copy and Adjust Sections
Next, we will need to make 3 copies of our 1st Quarter section and name them appropriately.
Click the gear icon for the 1st Quarter section.
Click Copy.
Type in 2nd Quarter.
Click Copy.
Repeat for each of the other quarters.
Updating the Copied Sections
Now we just have to update all of the sections that were just created from copying the 1st Quarter section. The only item which we will be changing is the Group Filter:
2nd Quarter should be Earliest Start Date is Between 04/01/2018 and 06/30/2018
3rd Quarter should be Earliest Start Date is Between 07/01/2018 and 09/30/2018
4th Quarter should be Earliest Start Date is Between 10/01/2018 and 12/31/2018
Creating the Totals Section
We are almost to the finish line! This part of the report is optional. This will add one section that shows you a quick overview of each of the 4 sections such as: new clients per quarter, and total new/unique clients.
Create a new Section and title it "Totals".
From the Special Columns, Summary Columns pull in 4 Section Count columns.
Click the gear for each one, change the dropdown in the "Choose a Section" area and pick each respective quarter and choose apply. This automatically changes the "Name" to the section it pulls data from. (repeat for each section).
Next, add a Number Calc column from the Special Columns, Calculations area. We will name this Unique Clients and "Add" each of the quarter columns (which are counting clients) and choose Apply.
That's it, we're all done! Please note, every year you will need to copy the report and make the proper filtering adjustments.