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]

there are multiple CONTRACTs per PERIOD. Maybe that is where the issue is? 

When I use Rohan's code, I am getting a cumulative total for the entire data set, as opposed to just the specific total per contract per period. 

[MAPSF1]

Yeah, I think what Rohan's code is doing is taking the previous 12 month's SALES, and summing that. It's not taking into consideration that it should only be for the particular CONTRACT, within that 12 month rolling period. 

[chrismarlow · Accepted solution]

Hi,

Change the end of the code to something like;

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

Next i;


LEFT JOIN (Data)
Load * resident Temp;

drop table Temp;

Cheers,

Chris.

[MAPSF1]

 This is exactly it. Thank you very much for your help, both Chris & Rohan. 

[Rohan]

Hi

I was about to write the same thing 😁, Only thing I would suggest is don't join the table in the end because then there would be multiple rows for ccSales  & the sum(ccSales) may  go wrong. Do check once before the join part.