Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Not applicable

full cummulated sum script

Hi Guys,

how can I use the cummulative sum in script like in expression:

rangesum(above(Sum(Quantity),0,rowno()))

I need the cummulated sum as a fix number, that can calulated forward or backward ..

I tried with Rangesum(Quantity,peek('Quantity',-1) )as AccuSumScript; but it doesn't work

1 Solution

Accepted Solutions
MVP
MVP

Re: full cummulated sum script

No, in that case, it would a bit different. Try like:

Load

Dim,

Value,

If(Dim=Peek(Dim),RangeSum(Peek(NewValue), Value), Value) as NewValue;

Load * Inline [

Dim, Value

1, 1

1, 2

1, 3

2, 100

2, 200

];

One thing you have to make sure that - source data is properly sorted.

11 Replies
MVP
MVP

Re: full cummulated sum script

Try like:

Load

Dim,

Value,

RangeSum(Peek(NewValue), Value) as NewValue;

Load * Inline [

Dim, Value

A, 1

B, 2

C, 3

D, 100

E, 200

];

Not applicable

Re: full cummulated sum script

I have forgot about the arcticles.

There are many articles, does it impair with this function?

"RangeSum(Peek(NewValue), Value) as NewValue;" didnt work:

RangeSum(Peek(NewValue), Quantity) as NewValue

MVP
MVP

Re: full cummulated sum script

No, in that case, it would a bit different. Try like:

Load

Dim,

Value,

If(Dim=Peek(Dim),RangeSum(Peek(NewValue), Value), Value) as NewValue;

Load * Inline [

Dim, Value

1, 1

1, 2

1, 3

2, 100

2, 200

];

One thing you have to make sure that - source data is properly sorted.

Not applicable

Re: full cummulated sum script

Yes, its order by Date ASC;

but I dont get the same values as in the expression

acutally I ve got many articleNo, dates -  on each date there are increase values and decrease values = quantity.

like

articledatequantitycummulated quantity
a101.01.2012Increase +10, Decrease - 5 =55
a102.01.2012Increase +10, Decrease - 5 =510
a102.02.2012Increase +10, Decrease - 5 =515
a2..11
a2..23
a2..36

If(articleNo=Peek(articleNo),RangeSum(Peek(NewValue), Quantity), Quantity) as NewValue // = cummulated quantity

didnt work , also instead of articleNo -> Date .

MVP
MVP

Re: full cummulated sum script

Try to share a sample data in excel and insert a column with expected output.

Re: full cummulated sum script

You need to first order by articleNo and then by Date. Something like this

Order By articleNo, Date;

Not applicable

Re: full cummulated sum script

what I want is, to get the same accumulated values, which are in the expressions, into the script.

Because, if I select the year = 2014 of the date, it depends on the very first value, which is from 2007 ..

Re: full cummulated sum script

I think it would be easier to work with raw data rather than already aggregated data that you have shared. Can you share few 100 lines of raw data??

Not applicable

Re: full cummulated sum script

here we go

Community Browser