topic How to calculate accumulate data from excel based on another column? in QlikView

How to calculate accumulate data from excel based on another column?

Wed, 25 Nov 2020 16:16:04 GMT

Hi everyone,

I'm new to the qlikview world and stuck on this problem I have:

I have three data (blue below) in Excel and I want to display in QVW as below with the new calculated column.

"Accumulate Credit" is calculated based on Agent and ascending Years.

I've tried:

1.rangesum(Credit, 0,rowno())

2. rangesum(sum(Credit),0,rowno())

3. rangesum(Credit, peek('Acc_Credit') as Acc_Credit

I'm really new at this so I'm not sure what is the exact difference for those functions. Any help would be great!

Thanks!!

Agent	Credit	Year	Accumulate Credit
A	1	2010	1
A	2	2012	3
A	3	2013	6
B	5	1997	5
B	2	2003	7

sunny_talwar — Wed, 26 Oct 2016 22:52:26 GMT

Try this:

Table:

LOAD RowNo() as RowNum,

Agent,

Credit,

Year

FROM

[https://community.qlik.com/thread/237796]

(html, codepage is 1252, embedded labels, table is @1);

FinalTable:

LOAD *,

If(Agent = Previous(Agent), RangeSum(Credit, Peek('Acc_Credit')), Credit) as Acc_Credit

Resident Table

Order By Agent, Year;

DROP Table Table;

Thu, 27 Oct 2016 18:24:45 GMT

Thank you so much for the reply, it worked great!!!

sunny_talwar — Thu, 27 Oct 2016 19:20:47 GMT

Awesome!!

Please close the thread by marking the correct response.

Best,

Sunny