Do do I rangesum with multiple dimensions?

[#1]

I have a dataset containing Year, Age, and Loss.

For example:

Year	Age	Loss
2007	2	50
2007	3	100
2009	6	25
2007	12	60
2008	15	70
2009	16	45
2010	18	65

And I want to create a line chart with Age on the x-axis and cumulative loss amount on the y-axis, and a separate line for each year.

So for 2007, age 1 would be 0, age 2 would be 50, age 3 would be 50 + 100 = 150, age 4 would be 150, ... age 12 would be 210, etc.

I created a line chart with Age as the first dimension and Year as the second.  The expression is

RangeSum (above(sum(Loss),0,RowNo()))

But this doesn't result in the right figures.  If I switch the chart to a table, the table looks like this:

Age	Year	Loss
2	2007	50
3	2007	150
6	2009	175
12	2007	235
15	2008	305

So I thought maybe it had something to do with sorting, so I sorted the data in my data model by year and then by age but this didn't fix the problem. 

What would the correct expression be for this?

Thanks!

[sunny_talwar]

Try this:

RangeSum(Above(TOTAL Sum(Loss), 0, RowNo(TOTAL)))

[#3]

Sunny,

Thank you for the response but this doesn't quite work either. It look like this results in a cumulative loss for all years combined. 

[sunny_talwar]

Is there a sample you can provide (may be a qvw where you have been working on) so that we can also play around with it a little

[#5]

Sure, here you go:

[sunny_talwar]

Not sure if you have QV12 or not, but I can probably help you with a script solution for QV11.2 or before if you can confirm that one of these is what you are looking for.

[#7]

I have QV 11.2

That second chart looks right.  The values for 2008 don't look right in the first chart.

[sunny_talwar]

So here is what you need to do. On there first occurrence, sort by Year field first followed by age field. and then you can use this expression:

Aggr(RangeSum(Above(Sum(Loss), 0, RowNo())), Year, Age)

[#9]

I'm not quite sure I understand what you mean when you say "On there first occurrence, sort by Year field first followed by age field."  I went into chart properties and selected 'Sort' but I don't see an option to sort Year first and Age second.

If instead I change the chart to a straight table then it allows me to sort by Year and then Age.

Here's what it looks like when I have the chart set to a line chart:

And then here's a straight table:

[sunny_talwar]

This will be needed to be done in the script. Would you be able to provide this Excel file so that I can reload your qvw file?

C:\Users\A104028\Desktop\New Claims Tracking\CAD\Formula Test\data.xlsx