I have a report that calculates if a service for a client started within the 3rd day of enrollment. The client may have multiple services that either did or didn't start by the 3st day of enrollment.
The report is grouped by client, then all services client receives, then date of initial service {@DOS) and enrollment date {@Enroll Date}.
I've done a formula that says: if {@DOS}-{@Enroll Date}<=2 then 1 else 0 - Call this the "3rd Day Field"
This gives me a 1 in the next column to identify if services were in compliance...so far so good.
Since the report needs to tell me if ANY services that the client received happened in this 3rd day. I've got the following example:
John Smith
Service 1 - Enroll Date - Service Date - "3rd Day Field"
Service 2 - Enroll Date - Service Date - "3rd Day Field"
Service 3 - Enroll Date - Service Date - "3rd Day Field"
Service 4 - Enroll Date - Service Date - "3rd Day Field"
Jane Doe
Service 1 - Enroll Date - Service Date - "3rd Day Field"
Service 2 - Enroll Date - Service Date - "3rd Day Field"
Service 3 - Enroll Date - Service Date - "3rd Day Field"
Service 4 - Enroll Date - Service Date - "3rd Day Field"
Randy Randerson
Service 1 - Enroll Date - Service Date - "3rd Day Field"
Service 2 - Enroll Date - Service Date - "3rd Day Field"
Service 3 - Enroll Date - Service Date - "3rd Day Field"
Service 4 - Enroll Date - Service Date - "3rd Day Field"
If there is a "1" in the 3rd Day field, I need to count this as a "1" for the group.
I've got this working, using a SUM of the "3rd Day Field" to calculate the MAXIMUM which is giving me the "1" I'm looking for for the result to see if the group had a "1" value in the "3rd Day Field"
I then get an Unduplicated Count of all client names in the report footer, which correctly calculates the total amount of clients.
Here is the problem...
I need to now calculate the amount of "1" by group and ADD them together to get a total of clients that are in compliance, then run a percentage calculation to give the % of clients within compliance.
Any thoughts?
Thanks
|