Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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.
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.