11 Replies Latest reply: Aug 27, 2015 9:22 AM by Tobias Meyer

# 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

• ###### Re: full cummulated sum script

Try like:

Dim,

Value,

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

Dim, Value

A, 1

B, 2

C, 3

D, 100

E, 200

];

• ###### 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

• ###### Re: full cummulated sum script

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

Dim,

Value,

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

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.

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

• ###### 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

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

• ###### Re: full cummulated sum script

Date,

//StockValueAccuExp,

If(Articleno=Peek(Articleno),RangeSum(Peek(StockValueAccuExp), "sum[stockValue (aktual)])"), "sum[stockValue (aktual)])") as StockValueAccuExp,

"sum[stockValue (aktual)])",

//QuantityAccuExp,

If(Articleno=Peek(Articleno),RangeSum(Peek(QuantityAccuExp),  [sum(Quantity)]),  [sum(Quantity)]) as QuantityAccuExp,

[sum(Quantity)]

FROM

....

(biff, embedded labels, header is 2 lines, table is Sheet1\$);

PFA

• ###### Re: full cummulated sum script

Now I get various values on each day, one of them is the right one. (excel result)

Because that in the sum(quantity) and sum(stockvalue ) from the excel, is sum into 1 value of each day..

but how can I sum that in script?

If I use sum(Quantity) or sum(stockvalue) in each if-conditions, = invalid expression error

• ###### Re: full cummulated sum script

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

Order By articleNo, Date;