Hello All,
I work for an insurance company and would like do produce a graph that shows claims costs, but where each claim is capped at a certain level, say £100,000.
My data looks something like this:
| Claim No. | Month | Cost |
|---|
| 12345 | 1 | 50000 |
12345 | 2 | 50000 |
| 12345 | 3 | 20000 |
| 12345 | 4 | -40000 |
| 12345 | 5 | 20000 |
I want a way of capping the costs at £100,000. I should point out that the total cost of the Claim is an accumulation of all the costs, so it is 100,000 at month 2, 120,000 at month 3 etc...
I cant remember my exact formula i tried, but it looked something like this:
RangeSum({}Sum({<>}If(Aggr(RangeSum(Above(Sum({<>}[Cost])),0,RowNo())>100000,100000,
Sum({<>}[Cost]),[Claim No.],[Month]))),0,RowNo())
In words, if the RangeSum is above 100000, put in 100000, otherwise do the sum, then do the RangeSum of this. It almost works, the problem is that when the RangeSum is above 100000, it puts in 100000 in for every line of the data, so when I accumulate this i add on too much. I think i somehow need to do this range sum if statement outside the range sum. Im stuck if im honest, any ideas will be greatly appreciated!!!
For the end result, i want a table with month in first column, possibly another dimension in second column (like product), and the capped per claim number cost in the third column.
Thanks,
Connor
