How to Calculate Shelter Nights
We have had many agencies call to ask where to find a calculation of shelter nights. This means that each night a client is served in their program counts as one shelter night. It is measured between two dates, so that the nightly client count served on each night in the date range is added together to give a total number of "shelter nights". It seems like a really straightforward idea that should be really easy to calculate, but the trick is cutting off program stays at either end of the date range. The way I built this report is using one query, on the Entry Exit Universe.
Use Client ID, Entry Date, Exit Date, and Provider ID. Filter inactive clients and Entry Exits, add in the usual date range filters (Entry Date less than Report End Plus One, Exit date either Null or greater than or equal to Report Start), and also a provider prompt.
Run the report, then create the following variables:
FormatEntryDate (this formats the Entry Date correctly)
=ToDate([Entry Exit Entry Date];"M/d/yyyy")
FormatExitDate (this formats the Exit Date correctly)
=ToDate([Entry Exit Exit Date];"M/d/yyyy")
PromptEnd-1 (this returns the actual Report End Date, one day prior to the date entered into the prompt)
1=RelativeDate(If(IsError(ToDate(UserResponse("Report End Date Plus One:");"M/d/yyyy")); 2ToDate(UserResponse("Report End Date Plus One:");"M/d/yyyy h:mm:s a"); 3ToDate(UserResponse("Report End Date Plus One:");"M/d/yyyy"));-1)
PromptStart (this returns the Report Start Date)
1=If(IsError(ToDate(UserResponse("Report Start Date:");"M/d/yyyy")); 2ToDate(UserResponse("Report Start Date:");"M/d/yyyy h:mm:s a"); 3ToDate(UserResponse("Report Start Date:");"M/d/yyyy"))
ShelterNights (this says to add the numbers of days in the program stays, but if the program stay started before the Report Start Date, then start counting at the Report Start Date, and if the program stay ended after the Report End Date, or didn't end at all, then stop counting at the Report End Date.)
1=Sum(DaysBetween( 2 If([FormatEntryDate] > [Prompt Start]; [FormatEntryDate]; [Prompt Start]); 3 If(([FormatExitDate] > [Prompt End -1] Or IsNull([FormatExitDate])); RelativeDate([Prompt End -1];1); 4 [FormatExitDate])))
The way I formatted the report was to show the Provider Name and date range in the Header, then give the total Shelter Nights up top, then to basically to be transparent about how the number was derived. I included a data block that included Client ID, Entry Date, Exit Date, Start Counting Date, Stop Counting Date, and then Days Counted. This way the user can tell which clients were counted for how many days during the reporting period.
I think the most confusing thing about this report is the use of Date fields and having to worry about the date formatting and subtracting one from the End Date prompt. The other curve ball is in the RelativeDate() function toward the end of the ShelterNight variable that returns the day after the actual Report End Date, better known as the Report End Date Plus One the user enters in the beginning. It has to be included this way because the DaysBetween function will not count the final day of the date range it is calculating.
Anyway, whether or not you completely understand the variables, it is ok to use what has been copied here in your own reports as long as you understand basically what they do, and then study the data that comes out to be sure everything is adding up correctly for your data to be sure it is correct. Make any modifications as necessary!
Hope this is helpful to someone out there!