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!

[#2]

Sure, here it is.

[sunny_talwar]

May be try like this:

Table:

LOAD Year*100+Age as Sort1,

  Year as Year1,

    Age as Age1,

    Loss

FROM

[data.xlsx]

(ooxml, embedded labels);

FinalTable:

LOAD Year1 as Year,

  Age1 as Age,

  Loss,

  Sort1 as Sort,

  AutoNumber(Age1, Year1) as Key

Resident Table

Order By Sort1;

DROP Table Table;

The chart below should work the way you would expect.

[#4]

I feel like we're getting close.  This certainly works for the dataset I provided but it does not work when I apply this logic to my real dataset.

In the dataset I provided, a subset of the data model looks like this:

Age	Year	Key	Sort	Loss
12	2007	1	200712	2
19	2007	2	200719	2499999
26	2007	3	200726	-2000000
51	2007	4	200751	265000
52	2007	5	200752	370000
53	2007	6	200753	8500
54	2007	7	200754	0
55	2007	8	200755	-45286.6
56	2007	9	200756	12488.82
61	2007	10	200761	468

In my actual data, the same subset of data would look like this:

Age	Year	Key	Sort	Loss
12	2007	12	200712	2
19	2007	19	200719	2499999
26	2007	26	200726	-2000000
51	2007	51	200751	265000
52	2007	52	200752	370000
53	2007	53	200753	8500
54	2007	54	200754	0
55	2007	55	200755	-45286.6
56	2007	56	200756	12488.82
61	2007	61	200761	468

As you can see, all columns are identical except for 'Key'.  The keys are difference because I have millions of records in my actual data, so I have a record for almost every year/age combination and therefore 2007 age 12 results in a key of 12 rather than 1.  I attached a QVW and data file showing what this ends up looking like in qlik.

[sunny_talwar · Accepted solution]

I think this should work out for you.

Script:

Table:

LOAD Year as Year1,

    Age,

    Loss

FROM

[data.xlsx]

(ooxml, embedded labels);

FinalTable:

LOAD Loss,

  Year1 as Year,

  Age,

  AutoNumber(Age, Year1) as Key

Resident Table

Order By Year1, Age;

DROP Table Table;

[#6]

That worked, thanks!