Multi-Row Formulas and Generate Rows - Help with Gaps!

[JohnMaty]

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!

[DataBlender]

Hi @JohnMaty

Please find workflow attached. I've trimmed the status update date to the first of the month and then calculated the min and max of this date for each matter ID.

I generate the extra rows for the missing months and join these back on to the original data. The processing status is then updated using a multi-row formula (as you suggested) for the 'unmatched' rows. Wasn't sure if you required the other fields as well.

[JohnMaty]

This looks awesome but I have a few questions.  I noticed May and June were blank.  DO you know why and how I can fix that?  Second, the Matter_ID represents 1 case.  Will this support multiple cases at once?  Do I need to do anything special to make this run down an entire table of multiple cases (Matter_IDs?) 

[DataBlender · Accepted solution]

@JohnMaty the summarize groups by matter ID first and the multi-row should have matter id checked in the 'group by' section. I originally forgot to include matter_id as a joining condition in the join but with those three things combined, you should be able to run this workflow for a long list of cases.

May and June were blank because they were the new rows that were generated. I've updated the workflow now so that all fields are populated.

[JohnMaty]

THis is awesome.  If I could give more stars I would.  Cheers!

[JohnMaty]

I have a quick question on this.  I noticed for the timeframes between the first and last event are perfect.  I need to have this carry the final case status to the current month, regardless of how old the case is.  For example:  This case goes CLOSED  in 7-1-2016 = Month.  I would need to have this continue to 8-1-2016 Closed, 9-1-2016 Closed, 10-1-2016 Closed.....3-1-2017 Closed.

Can you help?

[DataBlender]

@JohnMaty Is the attached what you were hoping for?

Previously we had used the minimum and maximum case date to use as our range of dates. I added a formula to calculate the 'maximum date' to be the first day of the current month.

The result is that the final entry gets carried forward until the current month:

[JohnMaty]

I am running into a few issues with this.  I am getting cases mixed together.  A case is designated by a single matter_ID.  It appears as though cases are getting each other's Matter_IDs. 

See the attachment.  When I place the single Matter_ID (case) into the process it works great.  THis is the out at the bottom of sheet one.  When I change the workflow to process two cases, I get them mixed together with one Matter_ID.  THis is the top of sheet 1.  Can I get some help?  It appears as though I need to group by Matter_ID.

[DataBlender]

@JohnMaty give this one a whirl, we'll get there! :)