Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Cumulative Percentages in a Chart

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 DimensionTime DimensionAnother DimensionXY
A1a12
A1b14
A2a23
A2b33
A3a34
A3b23
B1a23
B1b12
B2a34
B2b11
B3a34
B3b22

Which gives the following results:

Series DimensionTime DimensionPercentage
Cumulative
A1(1 + 1) / (1 + 4)(1 + 1) / (1 + 4)
A2(2 + 3) / (3 + 3)(1 + 1 + 2 + 3) / (1 + 4 + 3 + 3)
A3(3 + 2) / (4 + 3)(1 + 1 + 2 + 3 + 3 + 2) / (1 + 4 + 3 + 3 + 4 + 3)
B1(2 + 1) / (3 + 2)(2 + 1) / (3 + 2)
B2(3 + 1) / (4 + 1)(2 + 1 + 3 + 1) / (3 + 2 + 4 + 1)
B3(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.

1 Solution

Accepted Solutions
Not applicable
Author

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

View solution in original post

10 Replies
Not applicable
Author

Hi,

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

vincent_ardiet
Specialist
Specialist

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

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

Not applicable
Author

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

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

Not applicable
Author

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

swuehl
MVP
MVP

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

Not applicable
Author

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
Not applicable
Author

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