Exit Date vs Report End Date
Sometimes we need to look at client data that represents the most recent data available on a client. For HMIS, this would translate to showing data as of an Exit Date, or, if the client had not exited the program on the last day of the reporting period, the report would use the last date of the reporting period instead. The example I will use here is looking at a custom report that aims at counting how many clients maintained or increased any income during their program stay. It looks at leavers and stayers. For the leavers, it will compare their Total Monthly Income at Entry to the Total Monthly Income at Exit, whereas for the stayers, it will compare their Total Monthly Income at Entry to the Total Monthly Income as of the Report End Date. How to Code It?
The variable used to show the Exit Date or End Date, whichever is appropriate can simply be called End or whatever your naming convention is. It should be a basic If statement which shows the Exit Date if it is less than the Report End Date, and show the Report End Date if the Exit Date is null or greater than the Report End Date. Here is how mine looks:
1=If(IsNull([Clients].[Entry Exit Exit Date]) Or [Clients].[Entry Exit Exit Date] > [ReportEndDate]; 2 [ReportEndDate]; 3 [Clients].[Entry Exit Exit Date])
Reading it exactly from the beginning, the formula is saying if either the exit date is null or greater than the Report End Date, then return the Report End Date, otherwise, return the Exit Date.
Since this variable refers to the ReportEndDate variable, here's that one:
1=If(IsError(ToDate(UserResponse([Leavers];"Report End Date Plus One:");"M/d/yyyy")); 2ToDate(UserResponse([Leavers];"Report End Date Plus One:");"M/d/yyyy h:mm:s a"); 3ToDate(UserResponse([Leavers];"Report End Date Plus One:");"M/d/yyyy"))
The ReportEndDate variable is necessary so that the data collected from the prompts is considered a date for calculation purposes and is formatted correctly. This variable is simply copied from other ART reports written by Bowman. It is always written the same, and it is in almost every report I come across. Frankly I do not understand the "IsError" portion of this formula, but I know that this formula gets me the Report End Date in a date format, so that is what I use!
How to Display It?
One of the important ways of defining the date in that column clearly is to simply use the column heading; name it so that it is correct, but not too wordy. This can be tricky, but in the example below, I just went with "Exit Date or Report End Date".
The second way to indicate it is to set an alerter so that if the Exit Date is either null or greater than the Report End Date, it shows in a gray font. This way when the user is looking down the data block, they can immediately see which clients had not exited at the end of the reporting period.
In the image above, you can see the reporting period is basically all of 2013. In the Exit Date or Report End Date column, the grayed out "1/1/14" records are easy to pick out but not intrusively so. Keeping it the default font makes it look like a lot of clients happened to exit on 1/1/2014, and the only indication of anything otherwise is if the user reads and understands the column heading. Having the cell show no value at all makes it unclear what date the "Income at Exit or Report End Date" was calculated on.
There are likely other good ways to do this, and I'd be interested in hearing how others design their reports with the distinction of using the Exit Date or Report End Date in calculations.. Thanks so much for reading!