topic Re: full cummulated sum script in QlikView

full cummulated sum script

Thu, 27 Aug 2015 10:06:59 GMT

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

tresB — Thu, 27 Aug 2015 10:21:58 GMT

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

];

Re: full cummulated sum script

Thu, 27 Aug 2015 10:42:35 GMT

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

tresB — Thu, 27 Aug 2015 10:57:21 GMT

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.

Re: full cummulated sum script

Thu, 27 Aug 2015 11:25:56 GMT

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.

article	date	quantity	cummulated quantity
a1	01.01.2012	Increase +10, Decrease - 5 =5	5
a1	02.01.2012	Increase +10, Decrease - 5 =5	10
a1	02.02.2012	Increase +10, Decrease - 5 =5	15
a2	..	1	1
a2	..	2	3
a2	..	3	6

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

tresB — Thu, 27 Aug 2015 11:32:57 GMT

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

Re: full cummulated sum script

sunny_talwar — Thu, 27 Aug 2015 11:33:01 GMT

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

Order By articleNo, Date;

Re: full cummulated sum script

Thu, 27 Aug 2015 11:50:26 GMT

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

sunny_talwar — Thu, 27 Aug 2015 12:00:04 GMT

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

Thu, 27 Aug 2015 12:29:10 GMT

here we go

Re: full cummulated sum script

tresB — Thu, 27 Aug 2015 12:41:00 GMT

LOAD Articleno,

    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

Thu, 27 Aug 2015 13:11:33 GMT

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