Cumulative sum by month in load

[Antoine5531]

Hello,

I'm trying to add a variable to my data that would sum the values of all the previous month of this year + the actual month.

Here is an example :

Year	Month	id	Amount	New variable
2021	01	a	10	10
2021	02	b	10	20
2021	03	c	10	50
2021	03	d	10	50
2021	03	e	10	50
2021	04	f	10	60
2021	05	g	10	80
2021	05	h	10	80
2021	06	i	10	90

I manage to do it in a table but i'm struggling to do it in the script pls help.

Thanks.

[MayilVahanan · Accepted solution]

Hi

Try like below

Test1:
LOAD * INLINE [
Year, Month, id, Amount
2021, 01, a, 10
2021, 02, b, 10
2021, 03, c, 10
2021, 03, d, 10
2021, 03, e, 10
2021, 04, f, 10
2021, 05, g, 10
2021, 05, h, 10
2021, 06, i, 10
];

Join
Load *, SumAmount+Alt(Peek('NewVariable'),0) as NewVariable;
Load Year,Month, Sum(Amount) as SumAmount Resident Test1 group by Year, Month;

[aditya12345]

You can use the below - 

LOAD Year,
Month,
id,
Amount,
if(Year = previous(Year), Rangesum(Amount,peek([New variable])), Amount) as [New variable]
FROM
[<PATH>]
(ooxml, embedded labels, table is Sheet1);