These forums have been a saving grace for some of the work I have been asked to perform so thank you. I am stuck and have 2 questions. With the help of a forum member, I figured out a calculation that I added as a calculated field. It is as follows:
IF [Days in Application] > DATEDIFF( 'day', [Application Date],[Closure_Draft_Date] ) OR [Days in Application] > DATEDIFF('day', [Application Date],[Elig_Det_Ext_Date]) THEN [Days in Application] END. I am attaching a screen shot of what I hope this will look like. The purpose of this calculation is to only bring back those clients where the criteria was met, meaning, the number of days in application exceeded the allowed number of days. The first part looks for those that were determined ineligible and therefore a closure was started. The second checks to make sure the number of days in application status did not exceed the allowed 60 days plus any additional approved days with the extension. My question is how or where to add this calculated field to make it appear like my screen shot. Right now, I am only filtering those who went beyond 60 days in application status. When I remove that filter and add the calculated field all information disappears even though there are clients that breach the calculation and should appear. Would a column, row, filter, or mark be the best place to make this appear?
The second question I have is about creating a date. I have hooks like when an application was started, when a case was closed, when a client changes statuses etc. but nothing for a blank date. What I am trying to do is show how many open clients a counselor had at any given time over the course of a year. Ideally, this would show the counselor name, the months throughout the year I am looking at, and how many open clients they had during the month. This is meant to show trends. I have the ability to add the number of open clients, the counselors name, everything I need with the exception of the separation by month. I found Date() was an option, but I am missing how to use it.
Any help is greatly appreciated.