Cumulative Sum

[suhasinigm]

Hi Guru's,

I Calculated Cumulative_Sum in UI by using Range Sum,

but i am not getting how  to achieve the same in Script level.

Please help me to achieve this.

Please find  below sample data.

Year	Month	Sum_Sales	Cumulative sum
2017	Apr	120	220
2017	Mar	100	100
2018	Feb	100	200
2018	Jan	100	100

Thanks and Regards

Suhasini G Metri

[giovanneb]

Hi, try this

tmp:

LOAD * Inline [

Year, Month, Sum_Sales

2017, Apr, 120

2017, Mar, 100

2018, Feb, 100

2018, Jan, 100

];

tmp1:

LOAD

*,

if(Month =  'Jan', 1,

if(Month =  'Feb', 2,

if(Month =  'Mar', 3,

if(Month =  'Apr', 4,

if(Month =  'Mai', 5,

if(Month =  'Jun', 6,

if(Month =  'Jul', 7,

if(Month =  'Ago', 8,

if(Month =  'Set', 9,

if(Month =  'Out', 10,

if(Month =  'Nov', 11,

if(Month =  'Dez', 12 )))))))))))) as MonthNum

Resident tmp;

DROP Table tmp;

tmp2:

LOAD

*,

if(Year = Peek(Year), Sum_Sales + Peek(Sum_Sales), Sum_Sales) as Acm_Sales;

NoConcatenate

LOAD

*

Resident tmp1

Order by Year, MonthNum;

DROP Field MonthNum From tmp2;

DROP Table tmp1;

[Quy_Nguyen]

Try this:

A:
Load Year, Month, Date#(Year&'-'&Month, 'YYYY-MMM') As YearMonth, Sum_Sales;
LOAD * INLINE [
Year, Month, Sum_Sales
2017, May, 200
2017, Apr, 120
2017, Mar, 100
2018, Mar, 50
2018, Feb, 100
2018, Jan, 100];
Final:
Load Year, Month, Sum_Sales,     
 If(Peek(Year) <> Year,  Sum_Sales, Peek("Cumulative sum") + Sum_Sales ) As "Cumulative sum";
Load Year, Month, Sum_Sales , Sum_Sales as "Cumulative sum"
Resident A
Order by YearMonth;

Drop Table A;

[suhasinigm]

Thank You So much Giovanne.

I got the answer.

[suhasinigm]

Thank you so much Nguyen.

I got the answer.