Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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
Solved! Go to Solution.
Hi,
Try this link - https://drive.google.com/open?id=1dH-QEuEKH27RxBZlg2SoHfX5JL1zT1Cd
Hope this helps.
Hi,
I am inteested in helping. Share the link from where i can download your file.
I appreciate your willingness!
Please use this dropbox link for the sample file (I used Excel for the sample file, hope that is OK). The field "Loss Date" is the start date and the field "altClose Date" is the ending date. Also, if Status is "Final", then the claim record should be excluded from the counts, we are only worried about claim statuses other than "Final" (Open, Reopened).
https://www.dropbox.com/s/6urun199qkosbok/AnalyticsReport%20-%20Copy.xlsx?dl=0
Thank you kindly!
Hi,
Do you only want to see those where the loss date is on or after 1/1/2018 and where the status is other than Final?
Hi Ashish,
I'd like to consider the entire history of this dataset where status is other than Final.
I look forward to learning the technique so I can apply to other projects when needed!
I appreciate your continued help!
Hi,
Is this the result you are expecting?
Hi Ashish,
I appreciate your work, this looks promising!
Anyway you can share your techniques and logic please so I can further review?
Hi,
You may download my file from here.
Hope this helps.
I'm sorry, I can't download the file:
Hi,
Try this link - https://drive.google.com/open?id=1dH-QEuEKH27RxBZlg2SoHfX5JL1zT1Cd
Hope this helps.
Very nice work!!
I truly appreciate your help with this, and you taught me a thing or two!
You are welcome.
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
65 |
User | Count |
---|---|
135 | |
118 | |
101 | |
71 | |
61 |