topic Re: RangeSum Question in Chart in QlikView

RangeSum Question in Chart

timsaddler — Fri, 08 Apr 2016 10:46:13 GMT

Hi All

Struggling with the calculation of Cumulatives

As per screenshot , I need cumulatives of 8,16,24,32 for Cum Qnty

Currently I tried rangesum(above(sum(QUANTITY),0,rowno()))

Thanks in advance

Re: RangeSum Question in Chart

tresB — Fri, 08 Apr 2016 10:58:12 GMT

May be like:

rangesum(above(total sum(QUANTITY),0,rowno()))

Re: RangeSum Question in Chart

hic — Fri, 08 Apr 2016 11:00:27 GMT

I suspect that you should use

rangesum(above(total sum(QUANTITY),0,rowno()))

HIC

Re: RangeSum Question in Chart

sunny_talwar — Fri, 08 Apr 2016 11:02:18 GMT

Won't we need a TOTAL within RowNo() as well?

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

Re: RangeSum Question in Chart

timsaddler — Fri, 08 Apr 2016 11:06:29 GMT

I'm missing something here because it still doesn't work ? do I need to do something else ?

Re: RangeSum Question in Chart

sunny_talwar — Fri, 08 Apr 2016 11:09:19 GMT

Can you try this?

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

Re: RangeSum Question in Chart

timsaddler — Fri, 08 Apr 2016 11:14:21 GMT

Thanks that was very helpful. How do I keep the cumulative to each order no? Please see screenshot

Re: RangeSum Question in Chart

sunny_talwar — Fri, 08 Apr 2016 11:34:04 GMT

Now this makes the the requirement a little more challenging. The problem is that TOTAL identifier within RowNo() cannot be based on certain field (although the help says it can be, but somehow it doesn't work). So now Either you need a field from your script which is sequence of number for each order, or you need to use Aggr() function. Are you using QV12? If you are then the problem can be solved much easily (The sortable Aggr function is finally here!). If not then you again will have to sort your data correctly in the script to resolve this.

In short -> Are you using QV 12? If not, then be ready to make modification to the script.

Re: RangeSum Question in Chart

timsaddler — Fri, 08 Apr 2016 11:39:56 GMT

Hi thanks for that. I am using Qlikview 11.2.

Can you point me in the right direction of how to do this in the script please?

An example would be great if possible.

Thanks

Re: RangeSum Question in Chart

timsaddler — Fri, 08 Apr 2016 11:48:21 GMT

This seems to do the trick @Sunny T

Re: Rangesum aggregation script

Re: RangeSum Question in Chart

sunny_talwar — Fri, 08 Apr 2016 11:48:34 GMT

Does the accumulation needs to be by OrderNo and Date or Just by OrderNo?

Re: RangeSum Question in Chart

sunny_talwar — Fri, 08 Apr 2016 11:49:36 GMT

Awesome , I am glad you were able to figure it out. I am guessing you won't need a sample anymore?

Re: RangeSum Question in Chart

timsaddler — Fri, 08 Apr 2016 11:57:37 GMT

Thanks sunindia‌ you gave me just info to work it out myself which is great

Problem solved