Hi Everyone,
This is driving me absolutely crackers. I am responsible for generating statistical reports for our courts. The judges asked me to create a report that shows an "active case count" trend line month over month, year over year. This sounds simple but it is extremely difficult. I have attached a sample of data. The issue is this. A case is active when it comes into a court (Filing Dt) and remains active until some sort of action changes the status. This action may occur three months later (Status Update Dt). As a result, there are two months between when the last "Active Status" is recorded and the next "Adjudicated status" is recorded. I need to account for those gap months. How can I use generate rows to fill in the blank months and fill in the appropriate statuses? I am assuming I will need to Generate Rows and Multi-row formula. I am simply interested in Month level data and not day level. Also, some months can have multiple statuses within it.
Please help!
