Cumulative sum in script

[MAPSF1]

Hi All, 

I'm having some difficulty with summing some rows, in order to create a running 12 month total field. Because of the nature of the data, I have to create the result as a dimension. I want to show the total 12 months combined, for any given month. 

The data is shown like so, with my own added Expected Total column. 

PERIOD	YEAR	MONTH	CONTRACT	SALES		EXPECTED TOTAL
201210	2012	10	123	1		1
201211	2012	11	789	3		3
201212	2012	12	123	4		5
201301	2013	1	123	4		9
201302	2013	2	456	8		8
201303	2013	3	123	3		12
201304	2013	4	123	2		14
201305	2013	5	123	6		20
201306	2013	6	456	8		16
201307	2013	7	789	4		7
201308	2013	8	456	2		18
201309	2013	9	333	9		333
201310	2013	10	456	7		25
201311	2013	11	456	10		35
201312	2013	12	123	3		22
201401	2014	1	789	5		9
201402	2014	2	123	7		21
201403	2014	3	789	4		13
201404	2014	4	789	3		16
201405	2014	5	456	8		35
201406	2014	6	789	7		23
201407	2014	7	789	9		28
201408	2014	8	789	4		32
201409	2014	9	123	3		13
201410	2014	10	456	2		20
201411	2014	11	123	1		14
201412	2014	12	789	1		21

I have tried a few different methods, such as Peek & Above functions, with the main problem being when the year ends, and only the months within that year are included in the total, with the previous year not included. So I'm not sure if it's a sorting issue or not? 

Anybody able to point me in the correct direction?

Thanks in advance. 

[MAPSF1]

The following calculation works as a measure, but I cannot replicate it in the script; 

=sum(

Aggr(

RangeSum( above( TOTAL sum(SALES),0,12))

,[CONTRACT],[PERIOD_CONTRACT)

)

[MAPSF1]

I have also tried this without success, in the load script

Load

          RangeSum(Peek(SALES), Peek(SALES, -2), Peek(SALES, -3)....Peek(SALES, -12)) as TOTAL_SALES

[Rohan]

Hi,

Are you trying to get the rolling 12 month total against all the months ?

[Rohan]

Hi,

Please try the following code :

Data:
load *,
Date(date#(Period,'YYYYMM')) as PDate
inline [
Period, Year, Month, Contract, Sales
201210, 2012, 10, 123, 1
201211, 2012, 11, 123, 3
201212, 2012, 12, 123, 4
201301, 2013, 1, 123, 4
201302, 2013, 2, 123, 8
201303, 2013, 3, 123, 3
201304, 2013, 4, 123, 2
201305, 2013, 5, 456, 6
201306, 2013, 6, 456, 8
201307, 2013, 7, 456, 4
201308, 2013, 8, 456, 2
201309, 2013, 9, 456, 9
201310, 2013, 10, 456, 7
201311, 2013, 11, 456, 10
201312, 2013, 12, 789, 3
201401, 2014, 1, 789, 5
201402, 2014, 2, 789, 7
201403, 2014, 3, 789, 4
201404, 2014, 4, 789, 3
201405, 2014, 5, 789, 8
201406, 2014, 6, 789, 7
201407, 2014, 7, 789, 9
201408, 2014, 8, 789, 4
201409, 2014, 9, 789, 3
201410, 2014, 10, 789, 2
201411, 2014, 11, 789, 1
201412, 2014, 12, 789, 1];

T:
Load
min(PDate) as min,
Max(PDate) as max
Resident Data;

let min=num(peek('min',0,'T'));
let max=num(peek('max',0,'T'));
let diff=num(ceil(interval($(max)-$(min),'d')/30));

for i= 0 to $(diff)-1

Temp :
load
date(addmonths($(min),'$(i)')) as PDate,
sum(Sales) as ccsales
Resident Data where date(PDate)<=date(addmonths('$(min)',$(i))) and
date(PDate)>=date(addmonths(addmonths('$(min)',$(i)),-11));

Next i;

[MAPSF1]

Hi, 

Yes. So in each month, I would have the total of the previous 12 months, inclusive. 

[MAPSF1]

Anyone else able to help? 

The issue is persisting as the peek function is showing the previous rows in the load order, as opposed to the previous rows for the particular period and contract which it should be. 

creating that calendar didn't actually do anything for me. I was not returning any values as the SALES should be linked on CONTRACT & PERIOD, not the date. there is no date in my data. 

[Rohan]

 Hi,

We are creating the date using the Period field, Do you need Each Contract's Last 12 month Value at each transaction ?

[MAPSF1]

Hi, 

Yes, so I am looking to find the running total of the previous 12 months, for each CONTRACT in a given PERIOD. The PERIODS are loaded in order, but the CONTRACTS are not loaded in a particular order, so trying to order these is difficult as there is no defined order. Ideally, the data would be ordered by both PERIOD and CONTRACT, so that I can then sum the previous 12 months. 

[chrismarlow]

Hi,

Apart from the CONTRACT numbers getting a bit scrambled the code from @Rohan does seem to give the right expected result (you only have one CONTRACT per PERIOD in the original post, is this reflected in your actual data ... or is there something else going on?). The date has been created (as PDate) from your PERIOD to do date calculations on this.

Trying to use peek I think is going to be painful as you will need to peek back 12 rows, if you have missing periods this will fail, so approach above is better.

Cheers,

Chris.