Monthwise Culumative Sum in Script

[Flosom]

Hi All,

I'm trying to get a Cumulative sum of Monthwise Yearwise values in Load script.

Please suggest any methods. Tried to solve using Peek but was not successful.

sample data as below. Sales Accumulation is the field i'm trying to achieve. 

Sales Accum = Sum(Sales) for the year & Group for all months lesser that the row YEARMONTH 

Group	FIN YEAR	YEARMONTH	Sales	Sales Accum
Watch	2018	201801	1	1
Watch	2018	201802	3	4
Watch	2018	201803	5	9
Watch	2019	201901	7	7
Watch	2019	201902	9	16
Watch	2019	201903	11	27
Watch	2020	202001	13	13
Watch	2020	202002	15	28
TV	2018	201801	17	17
TV	2018	201802	19	40
TV	2018	201803	21	57
TV	2019	201901	23	23
TV	2019	201902	25	48
TV	2019	201903	27	75
TV	2020	202001	29	29
TV	2020	202002	31	60
Mobile	2018	201801	33	33
Mobile	2018	201802	35	68
Mobile	2018	201803	37	105
Mobile	2019	201901	39	39
Mobile	2019	201902	41	80
Mobile	2019	201903	43	123
Mobile	2020	202001	45	45
Mobile	2020	202002	47	92

thanks,

Florence

[SerhanKaraer · Accepted solution]

Hello Flosom,

You can achieve it with Peek, but your data must be sorted before applying cumulation operations.

LOAD Group, FINYEAR, YEARMONTH, Sales,
if(RecNo() = 1 or Previous(Group) <> Group or Previous(FINYEAR) <> FINYEAR, Sales, Peek("Sales Accum") + Sales) as "Sales Accum"
INLINE [
Group	FINYEAR	YEARMONTH	Sales
Watch	2018	201801	1
Watch	2018	201802	3
Watch	2018	201803	5
Watch	2019	201901	7
Watch	2019	201902	9
Watch	2019	201903	11
Watch	2020	202001	13
Watch	2020	202002	15
TV	2018	201801	17
TV	2018	201802	19
TV	2018	201803	21
TV	2019	201901	23
TV	2019	201902	25
TV	2019	201903	27
TV	2020	202001	29
TV	2020	202002	31
Mobile	2018	201801	33
Mobile	2018	201802	35
Mobile	2018	201803	37
Mobile	2019	201901	39
Mobile	2019	201902	41
Mobile	2019	201903	43
Mobile	2020	202001	45
Mobile	2020	202002	47
] (delimiter is '\t');

 I hope it solves your problem.