Create a Cumulative Claims Triangle

Hi,

I am trying to create a loss table with a pivot table. I managed to extract my data by Underwriting year and quarter of delay. So I have a triangle in place but I need to make the pivot table cumulative. I tried using the running total option as my values in the pivot table and it works but the running total continues for dates that are in the future.

Each year (rows) should be cumulative over the quarter of delays (columns) but it can't accumulate into the future!

I still need it to be in pivot table mode as I want to use the filter and slicer options, but I essentially need to make the running total values conditional on points that are available in the data. I saw a solution on a tablaeu forum but I need to do it in Excel. I will attach the format I need below.

Please ask if anything I say isn't clear.

Attach a sample workbook (not a picture or pasted copy). Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

Okay So I set up some arbitrary data-points with a example Pivot Table as well as my attempt and the required attempt specified in the worksheets. Each sheet also has Text Boxes to explain the problem and needs.

Thank you for your help!