topic Re: Cumulative sum in QlikView

Cumulative sum

Tue, 15 Apr 2014 05:43:25 GMT

Hi,

I have a question regarding cumulative sum of the data. I want to realize the following function:

original table:

a
1

1
1

Output table:

a	obs	Cum
1	1	1
	2	1
1	3	2
1	4	3
	5	3

Anyone can help?

Thanks!

Re: Cumulative sum

Tue, 15 Apr 2014 05:50:28 GMT

can you post some sample data please. Not sure how the dimension is segregated when it has just 1 unique value.

Re: Cumulative sum

er_mohit — Tue, 15 Apr 2014 05:50:51 GMT

try this in expression side

for obs fieldname write rowno()

rangesum(above(sum(Fieldname),0,rowno()))

Re: Cumulative sum

Tue, 15 Apr 2014 06:17:56 GMT

You could also try this (assuming that your original table has some sort of 'key' field), it will allow you to make a selection and still see the cumulative value.

sum(aggr(rangesum(above(total sum({<key=>} a),0,RowNo())),key))

I recommend this tutorial:

Calculating rolling n-period totals, averages or other aggregations

http://community.qlik.com/docs/DOC-4252

Cheers,

Paul

Re: Cumulative sum

MK_QSL — Tue, 15 Apr 2014 06:24:55 GMT

Please check enclosed file..

Re: Cumulative sum

Tue, 15 Apr 2014 06:41:34 GMT

can you post the code cause i am not able to access that qvw file.

Thank you

Re: Cumulative sum

MK_QSL — Tue, 15 Apr 2014 06:45:57 GMT

Script..

Load * Inline

[

ID,a

1, 1

3, 1

4, 1

];

Create a straight table

Dimension = ID

Expression

1) RowNo()

2) RangeSum(Above(Sum(a),0,RowNo()))

Re: Cumulative sum

Tue, 15 Apr 2014 06:54:22 GMT

How can I write this all in the script? I was trying to write them in the script but it doesn't recognize the "Above" function...

Re: Cumulative sum

anbu1984 — Tue, 15 Apr 2014 07:28:45 GMT

Cumulative_sum:

Load * Inline

[

ID,a

1, 1

3, 1

4, 1

];

table:

load

RowNo() as Sequence1,

RangeSum(a, Peek('CumSum')) AS CumSum,

Resident Cumulative_sum

order by ID, a;

Re: Cumulative sum

MK_QSL — Tue, 15 Apr 2014 08:21:46 GMT

You need to create a straight table...!!!

Script..

Load * Inline

[

ID,a

1, 1

3, 1

4, 1

];

Create a straight table

Dimension = ID

Expression

1) RowNo()

2) RangeSum(Above(Sum(a),0,RowNo()))

Re: Cumulative sum

Tue, 15 Apr 2014 08:34:49 GMT

why do I must create a straight table? I have a hundred of thousands of the data to assign these values, and then I need to do statistics on it, so I don't think it's realistic to display everything in the table...