C
Chuck Elsham
Hi there
I have a pivot table which has rows being trading year and columns
being revenue period (year 1, year 2 etc)
The industry is insuraance so premiums can arrive years later than the
polcy year.
1 2 3 4
2001 aaa bbb ccc ddd
2002 xxx yyy zzz
2003
2004 ppp
My figures are populated correctly, I have used letters as
placeholders in the example.
The intersection 2001 / 1 (shown as 'aaa') represents premiums
paid on 2001 contracts in the 1st year (ie 2001).
Premiums on the same contracts that arrived in 2002 are shown as bbb,
this being the 2nd year relative to 2001 contract.
You can see that at any point in time there will be more columns for
earlier years, beacuse more periods have elapsed for these years.
The data therefore represents a triangle.
My problem is that I really want to show this info as running totals,
but when I do so, the data gets carried across the whole row and I
lose the triangle shape.
Is there a way I can programatically create a running total that is
only applied if
(PolicyYear+Period-1) <= Year(Today)
Many thanks in advance
Rob
I have a pivot table which has rows being trading year and columns
being revenue period (year 1, year 2 etc)
The industry is insuraance so premiums can arrive years later than the
polcy year.
1 2 3 4
2001 aaa bbb ccc ddd
2002 xxx yyy zzz
2003
2004 ppp
My figures are populated correctly, I have used letters as
placeholders in the example.
The intersection 2001 / 1 (shown as 'aaa') represents premiums
paid on 2001 contracts in the 1st year (ie 2001).
Premiums on the same contracts that arrived in 2002 are shown as bbb,
this being the 2nd year relative to 2001 contract.
You can see that at any point in time there will be more columns for
earlier years, beacuse more periods have elapsed for these years.
The data therefore represents a triangle.
My problem is that I really want to show this info as running totals,
but when I do so, the data gets carried across the whole row and I
lose the triangle shape.
Is there a way I can programatically create a running total that is
only applied if
(PolicyYear+Period-1) <= Year(Today)
Many thanks in advance
Rob