Hi everyone!
I need to flag each month between two dates in my table so I can make a Matrix.
Situation:
Insurance adjusters have open claims. If the claim started on 1/1/18 and closed on 3/31/18, then the claim was open in Jan, Feb, and Mar, but not in April. I'd want that adjuster's row on the matrix to look something like this:
Number of Open Claims per Month:
| adjustername | Jan | Feb | Mar | April |
| JaneDoe | 1 | 1 | 1 | 0 |
That example the adjuster has only a single claim, but in reality adjusters will have hundreds of open claims.
The claim is a single record in the table with an open date and a close date and should be counted each month it is open. There are of course cases where the claim isn't closed yet, in those cases I use the last day of the previous month using logic, got that worked out. The dataset is always valued last day of previous month by the way.
In many cases claims are open several years. In the matrix, I'd only select the current calendar year for display, maybe last two calendar years, but I'd like to consider the entrie dataset so Management can view ranges.
How in the world would I get this done? Maybe it's simple and I'm overthinking it? I suspect another table may be used, and I can use Claim Number (unique) as an indexing key between the two tables.
Thanks so much for reading!
Kyle
