topic Cumulative Percentages in a Chart in QlikView

Cumulative Percentages in a Chart

Fri, 14 Oct 2011 13:25:43 GMT

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.

Cumulative Percentages in a Chart

Fri, 14 Oct 2011 13:29:16 GMT

Hi,

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

Cumulative Percentages in a Chart

vincent_ardiet — Fri, 14 Oct 2011 13:41:40 GMT

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

Cumulative Percentages in a Chart

swuehl — Fri, 14 Oct 2011 14:00:30 GMT

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

Re: Cumulative Percentages in a Chart

Fri, 14 Oct 2011 14:23:38 GMT

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

Re: Cumulative Percentages in a Chart

swuehl — Fri, 14 Oct 2011 14:29:53 GMT

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

Re: Cumulative Percentages in a Chart

Fri, 14 Oct 2011 14:38:58 GMT

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

Re: Cumulative Percentages in a Chart

swuehl — Fri, 14 Oct 2011 14:50:27 GMT

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...

Re: Cumulative Percentages in a Chart

Fri, 14 Oct 2011 14:54:53 GMT

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.

Re: Cumulative Percentages in a Chart

Anonymous — Tue, 14 Jul 2015 15:31:42 GMT

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])

Re: Cumulative Percentages in a Chart

swuehl — Tue, 14 Jul 2015 21:03:40 GMT

Sorry, this thread is almost 4 years old and your expression is missing context (like how the fields are related in your data model).

Please create a new thread and attach a sample QVW or some example data as shown above.

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

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

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