I am trying to accomplish the same thing and have done so, but in reverse using the book date on the X axis...but unlike the buckets the correct values are not in their appropriate place for real comparison. I am actively working on this now, and if you have found a way to solve for it please let me know!
Is the attached what you need?
Here is how I put it together:
1) Create calculated field with the same formula you are using to calculate the running sum:
Running Sum of [Incurred Indemnity and Expenses] = RUNNING_SUM(SUM([Incurred Indemnity and Expenses]))
2) Create another calculated field that will display the above only if there are records in the data for that particular month/bin combination:
[Display of Running Sum] = IF WINDOW_MAX(MAX([Number of Records])) > 0 THEN [Running Sum of [Incurred Indemnity and Expenses]]] END
This is what you need to show on your table.
The trick is to use the edit table calculation dialog and set the [Display of Running Sum] to compute using cell while computing [Incurred Indemnity and Expenses] using [Transaction days after loss (bin)]
You do this by choosing each field from the calculated field drop down in the edit table calculation dialog.
Keep in mind this relies on the presence of data for all valid bin/month combinations. If it's possible to have missing data in some months for which the combination is valid, you would have to modify this solution (or complete the data so it works).
Let me know if this works.
Claims_copy_pm.twbx.zip 2.6 MB
You are seriously a genius! Thank you for the help!
I have a similar issue as this and I have been successful except for where have missing month / development days combinations. If there is no activity on any claims from 90-120 days for a specific month, there is no data for that row column combination and the cell shows as blank. For the next period, if there is activity the table calculation continues to work.
Is there a way to fill in these blanks when there are no records?
This post was super helpful in creating loss triangles. My requirement is to take this calculation further and calculate weighted averages. What I am trying to achieve is dividing sum of A by sum of B as shown in
attached image. Please notice that we are not considering the last value of the triangle from the first time bin in calculation. Any help is much appreciated.