4 Replies Latest reply: Sep 12, 2018 2:07 PM by Suhasini Metri

Cumulative Sum

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 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

• Re: Cumulative Sum

Hi, try this

tmp:

LOAD * Inline [

Year, Month, Sum_Sales

2017, Apr, 120

2017, Mar, 100

2018, Feb, 100

2018, Jan, 100

];

tmp1:

*,

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:

*,

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

NoConcatenate

*

Resident tmp1

Order by Year, MonthNum;

DROP Field MonthNum From tmp2;

DROP Table tmp1;

• Re: Cumulative Sum

Thank You So much Giovanne.

I got the answer.

• Re: Cumulative Sum

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;

```
• Re: Cumulative Sum

Thank you so much Nguyen.

I got the answer.