Cumulative 12 months Sum on load script

[tduarte]

Hi all,

I need to create a cumulative 12 months Sum(Revenue) on the load script, aggregated by month, category and reseller.

Meaning that for each month, the sum should be based on up to the 12 previous months.

Month	Revenue	Cumulative12
Jan-12	10	10
Feb-12	10	20
Mar-12	10	30
Apr-12	10	40
May-12	10	50
Jun-12	10	60
Jul-12	10	70
Aug-12	10	80
Sep-12	10	90
Oct-12	10	100
Nov-12	10	110
Dec-12	10	120
Jan-13	10	120
Feb-13	10	120
Mar-13	10	120
Apr-13	10	120
May-13	10	120
Jun-13	10	120
Jul-13	10	120
Aug-13	10	120
Sep-13	10	120
Oct-13	10	120

The table contains many fields but I want the cumulative12 Sum, that will be used for banding, to be aggregated by month, category and  reseller.

I had a look at rangesum and peek not sure it will work here. I think it can be achieve with joins and group by.

Thanks.

[tduarte]

Thanks Erica,

Option 3 does the job but after I had to change the cumulative sum to a temp table then inner join it with the original one to remove records added with the outer that represented no sales:

//Add in the previous 12 months total

outer join (GenData) Load Cat1, Cat2,  Month + 12 as Month , Month  as MonthL12, Sales as SalesL12 resident GenData;

//Do the usual cumulative sum - but minus the value from 12 months ago

CumulativeTemp:

Load Cat1, Cat2, Month,

  if(Peek(Cat1)&peek(Cat2) = Cat1&Cat2, numsum(Sales,Peek(CumSum) ),Sales) as CumSum,

  if(Peek(Cat1)&peek(Cat2) = Cat1&Cat2, numsum(Sales,Peek(CumSumL12) ,-SalesL12 ),Sales) as CumSumL12,

  if(not(isnull(Sales)),1) as IsActualSales

resident GenData;

//Remove records where there were no actual sales (added with outer join for missing months)

Inner Join (GenData)

Load Cat1, Cat2, Month, CumSumL12

Resident CumulativeTemp

Where IsActualSales;

Drop Table CumulativeTemp;