topic Re: accumulative sum? in QlikView

accumulative sum?

Anonymous — Fri, 08 May 2015 07:01:52 GMT

hi friends,

ı want to use accumulative sum, but not from the "accumulation" option in the properties tab,

because when you do like that , it is not static, it calculates again when you filter the terms.

ı want ıt to be static. so this has to be done by expression I think.

how can we do?

Re: accumulative sum?

sunilkumarqv — Fri, 08 May 2015 07:05:22 GMT

@Hi fislant

in expression

Calculating rolling n-period totals, averages or other aggregations

Accumulative Sums

Re: accumulative sum?

jonathandienst — Fri, 08 May 2015 07:16:26 GMT

Do it like this:

Build an expression that sums your metrics up to the min value on the chart. Lets call that <expr1>
Then you need an expression that accumulates win the chart. Lets call your existing expression <expr2>. The accumulation will be of the form:
So your final expression will be of the form:

For more specific help, I suggest that you post a sample qvw file that contains the base expression (<expr2> above).

HTH

Jonathan

Re: accumulative sum?

Anonymous — Fri, 08 May 2015 10:50:52 GMT

jonathan thanks for your effort,

but ı have difficulties,

could you help about the expressions?

my table is likte that

stock value , customer , term

10 a 201401

20 a 201401

30 b 201402

40 c 201403

50 c 201404

ı think ı have to use, stock value and term columns.

Re: accumulative sum?

awhitfield — Fri, 08 May 2015 14:14:20 GMT

Hi fisland92,

see if the attached is what you want

HTH

Andy

Re: accumulative sum?

Anonymous — Tue, 12 May 2015 12:18:10 GMT

None of the above worked sorry,

when you filter it , it sums the filtered area not the total,

ı want the cumulative total to be static.

Re: accumulative sum?

Tue, 12 May 2015 13:48:20 GMT

Hi,

If you like it to be a static value would it perhaps be best to calculate when you run the script or using a set analysis expression to get a number that dont change upon selections.

Re: accumulative sum?

Anonymous — Wed, 13 May 2015 13:11:13 GMT

ıt can be done with load script but anyway you can not filter the data then.

so ıt should be done with accumulate expression but we could not fix it, it is always dynamic when you select certain terms the sum changes.

Re: accumulative sum?

Wed, 13 May 2015 15:52:54 GMT

You need to exclude the fields for which selections should impact your result from the expression.

For example in the expression provided by Andrew if you do not want selections in customer to impact expression result then use customer= in the set expression like this:

RangeSum(Above(Sum({<customer=>}[stock value]), 0, RowNo()))

Re: accumulative sum?

Anonymous — Wed, 13 May 2015 16:02:37 GMT

nice but it does not effect the month column.

also I can not change the sort order. the formula makes ascending months cumulative but ı need descending months.