topic Re: Do do I rangesum with multiple dimensions? in QlikView

Do do I rangesum with multiple dimensions?

Mon, 11 Jul 2016 11:56:45 GMT

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!

Re: Do do I rangesum with multiple dimensions?

sunny_talwar — Mon, 11 Jul 2016 12:35:57 GMT

Try this:

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

Re: Do do I rangesum with multiple dimensions?

Mon, 11 Jul 2016 12:41:44 GMT

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.

Re: Do do I rangesum with multiple dimensions?

sunny_talwar — Mon, 11 Jul 2016 13:21:26 GMT

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

Re: Do do I rangesum with multiple dimensions?

Mon, 11 Jul 2016 13:40:37 GMT

Sure, here you go:

Re: Do do I rangesum with multiple dimensions?

sunny_talwar — Mon, 11 Jul 2016 13:50:38 GMT

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.

Re: Do do I rangesum with multiple dimensions?

Mon, 11 Jul 2016 13:55:08 GMT

I have QV 11.2

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

Re: Do do I rangesum with multiple dimensions?

sunny_talwar — Mon, 11 Jul 2016 14:18:50 GMT

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)

Re: Do do I rangesum with multiple dimensions?

Mon, 11 Jul 2016 14:23:42 GMT

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:

Re: Do do I rangesum with multiple dimensions?

sunny_talwar — Mon, 11 Jul 2016 14:44:57 GMT

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

Re: Do do I rangesum with multiple dimensions?

Mon, 11 Jul 2016 14:46:41 GMT

Sure, here it is.

Re: Do do I rangesum with multiple dimensions?

sunny_talwar — Mon, 11 Jul 2016 16:06:19 GMT

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.

Re: Do do I rangesum with multiple dimensions?

Mon, 11 Jul 2016 17:41:39 GMT

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.

Re: Do do I rangesum with multiple dimensions?

sunny_talwar — Mon, 11 Jul 2016 19:47:36 GMT

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;

Re: Do do I rangesum with multiple dimensions?

Tue, 12 Jul 2016 11:13:45 GMT

That worked, thanks!