Cumulative Percentages in a Chart

[#1]

I have a chart with an expression of SUM(x) / SUM(y) that gives me results as percentages.

If I turn on "Full Accumulation", it sums the percentages.

What I would like is for each SUM() in my expression to sum the values from the preceding data points.

Example Data:

Series Dimension	Time Dimension	Another Dimension	X	Y
A	1	a	1	2
A	1	b	1	4
A	2	a	2	3
A	2	b	3	3
A	3	a	3	4
A	3	b	2	3
B	1	a	2	3
B	1	b	1	2
B	2	a	3	4
B	2	b	1	1
B	3	a	3	4
B	3	b	2	2

Which gives the following results:

Series Dimension	Time Dimension	Percentage	Cumulative
A	1	(1 + 1) / (1 + 4)	(1 + 1) / (1 + 4)
A	2	(2 + 3) / (3 + 3)	(1 + 1 + 2 + 3) / (1 + 4 + 3 + 3)
A	3	(3 + 2) / (4 + 3)	(1 + 1 + 2 + 3 + 3 + 2) / (1 + 4 + 3 + 3 + 4 + 3)
B	1	(2 + 1) / (3 + 2)	(2 + 1) / (3 + 2)
B	2	(3 + 1) / (4 + 1)	(2 + 1 + 3 + 1) / (3 + 2 + 4 + 1)
B	3	(3 + 2) / (4 + 2)	(2 + 1 + 3 + 1 + 3 + 2) / (3 + 2 + 4 + 1 + 4 + 2)

Is there a standard way to accomplish this in QlikView without doubling the number of fields in the data?  (field1 AND cumulativeField1, etc, etc)

Cheers,

Mat.

[#2]

Hi,

You could probably use Chart Inter Record functions. Help page has some examples on this.

[vincent_ardiet]

Hi,

As Hoekstra said, use inter record functions, in your case something like :

above(sum(X),0,rowno()) / above(sum(Y),0,rowno())

Regards,

Vincent

[swuehl]

As Robert and Vincent both mentioned, chart inter records will do, I would just add a rangesum to Vincents expression to get a sum of the appropriate row values:

=rangesum(above(sum(X),0,rowno())) / rangesum(above(sum(Y),0,rowno()))

Regards,

Stefan

[#5]

Thanks.

This is almost there.  There are very stange things happening where I have two dimensions on the chart (Time and Series).

It appears to be mixing up the data between the different series.

But I'm making progress now.  I think

[swuehl]

So what is happening? Please note that the order of dimensions and the pivoting if using pivot table chart is affecting the results or the need to change above e.g. in before.

Please see above for a sample.

Regards,

Stefan

[#7 · Accepted solution]

The problem is when I use a line chart.  I've updated your file to demonstrate...

[swuehl]

Yes, you changed the order of dimensions, that's quite important for the column segments where the rangesum will sum the values in.

You could use

=aggr(RANGESUM(ABOVE(SUM(X), 0, ROWNO())) / RANGESUM(ABOVE(SUM(Y), 0, ROWNO())), [Series Dimension],[Time Dimension])

to correct this.

But then you might have a problem with the line chart (QV Line chart is not very clever done IMHO). I changed it to bar chart, that looks fine to me.

Regards,

Stefan

edit: my mistake, changing back to line chart should work also...

[#9]

You sir, are a super massive exploding ball of hydrogen.

A star.

I can see that I have alot of reading to do regarding these functions.

Thanks,

Mat.

[Anonymous]

Great post. I have come to a problem though. As soon as I add set analysis in the sum it stops working. Any idea why this could be happening?

=aggr(RANGESUM(ABOVE(SUM({<[Hit Decision]={'control-group'}>}[Transaction Counter]*[Net Reporting Amount Gross]), 0, ROWNO()))

,[Visit Date])