topic Re: Cumulative sum (help) in QlikView

Cumulative sum (help)

Wed, 25 Nov 2020 16:16:04 GMT

Hi all,

In Pivot table, I am having issue with rcumulative sum; rangesum(above(sum(QTY),0,RowNo()) ; I have no idea why this forrmula doesn't work as it didn't perform the running sum ( 6th column)

as can see in the attached photo below, I would like to get the cumulative sum by product (the last column)

rangesum(above(sum(QTY),0,RowNo())

Re: Cumulative sum (help)

Digvijay_Singh — Sun, 21 May 2017 13:52:07 GMT

Try Total<Product> where you have used Total in expression.

Re: Cumulative sum (help)

Anil_Babu_Samineni — Sun, 21 May 2017 13:59:32 GMT

May be this?

Rangesum(Below(Sum(QTY),0),Above(QTY,1,RowNo()))

Re: Cumulative sum (help)

Sun, 21 May 2017 15:18:03 GMT

😞 it's stil not working

Showing null value

Re: Cumulative sum (help)

Sun, 21 May 2017 15:19:35 GMT

Value same as rangesum(above sum(qty),0, RowNo()))

Re: Cumulative sum (help)

Anil_Babu_Samineni — Sun, 21 May 2017 15:31:32 GMT

May be check this file? This is bad approach for your output. But, O/P Deserve

Re: Cumulative sum (help)

Sun, 21 May 2017 15:43:36 GMT

Thanks for your reply

The last output isn't what I want

I wish to get (by product regardless other dimensions)

Re: Cumulative sum (help)

Anil_Babu_Samineni — Sun, 21 May 2017 16:15:35 GMT

I have not tested this?

Aggr(RangeSum(Above(Sum(Qty),0,RowNo(TOTAL))),Product, ORderCode1, OrderCode2)

Similar i got some thing

Running totals per dimension (RangeSum(), sortable Aggr()...)

Re: Cumulative sum (help)

sunny_talwar — Sun, 21 May 2017 16:19:59 GMT

Do you have QV12 by any chance, if you do then you can do this

1) Script change

LOAD *,

(ORderCode1+OrderCode2/100) as NewOrderCode;

Load * Inline [

Product, ORderCode1, OrderCode2, ShipDate, Qty

A,101,1,21/5/17,2

A,101,2,21/5/17,3

A,111,1,21/5/17,4

B,104,1,21/5/17,2

B,106,2,21/5/17,3

B,104,2,21/5/17,4

];

and then an expression like this

Aggr(RangeSum(Above(Sum(Qty), 0, RowNo())), Product, (NewOrderCode, (NUMERIC)))

Where I am using a The sortable Aggr function is finally here! which is only available in QV12 and higher

NOTE: Used the attachment from Anil's post (thanks Anil)

Re: Cumulative sum (help)

Anil_Babu_Samineni — Sun, 21 May 2017 16:24:17 GMT

Sample attached, May be helps to you