Summing Previous Months

[#1]

Hi Community,

I have a field that has a value in period 0 that is the begining amount of 100.  Some of the following periods either add or subract from this and have values like -4, or 7.  How do I get the value desired below given the table below:

Year	Period	Values in Field	Value Desired
2011	0	100	100
2011	1	0	100
2011	2	3	103
2011	3	0	103
2011	4	0	103
2011	5	-4	99
2011	6	7	106
2011	7	0	106
2011	8	0	106
2011	9	5	111
2011	10	0	111
2011	11	2	113
2011	12	-1	112
2012	1	0	112
2012	2	0	112
2012	3	0	112

[#2]

i.e. Can I do this with set analysis or in the script loading?

[swuehl]

Well, if you can do it in the script, do it in the script, maybe using something like:

TEST:

LOAD *, rangesum([Values in Field],peek([ValueDesired])) as ValueDesired;

LOAD Year,

     Period,

     [Values in Field],

     [Value Desired]

FROM

[http://community.qlik.com/thread/52426?tstart=0]

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

Or create a straight table chart with dimensions Year and Period and as expression:

=rangesum(above(total [Values in Field],0,RowNo(total )))