Yearly cumulative sum in Load Script: Cumulation restarts for every year

[nezuko_kamado]

I was able to cumulate monthly sum in load script, but how to make the cumulation restarts for each year in load script?

RangeSum(peek('Cumulative') ,Monthsum) AS Cumulative

I used the code, which calulates 'Monthsum' across  all years(left), but what I need is restacking for the begining of each year(right)

The below is the script 

RangeSum(peek('Cumulative') ,Monthsum) AS CumulativeCumulatedTable_Temp:
load
MonthYear,
Month(MonthYear) as Month1,
Year(MonthYear) as Year1,
sum(Unit) as Monthsum
Resident Dat2
group By MonthYear;

TABLE:
LOAD
MonthYear,
Year1,
Month1,
RangeSum(peek('Cumulative') ,Monthsum) AS Cumulative
RESIDENT CumulatedTable_Temp;

and here's data

Dat2:
Load *,
Date#(dateFormat,'YYYYMMM') as MonthYear; 
load * inline [ dateFormat,          Unit
2019Jan,             2
2019Jan,             3
2019Jan,             4
2019Jan,             5
2019Feb,            2
2019Feb,            2
2019Feb,            1
2019Feb,            3
2019Feb,            5
2019Mar,           6
2019Mar,           4
2019Mar,           2
2019Mar,           5
2019Apr,            7
2019Apr,            2
2019Apr,            5
2019Apr,            3
2020Jan,             6
2020Jan,             2
2020Jan,             2
2020Jan,             3
2020Feb,            3
2020Feb,            4
2020Feb,            5
2020Feb,            6
2020Feb,            1
2020Mar,           1
2020Mar,           2
2020Mar,           2
2020Mar,           3
2020Apr,            1
2020Apr,            3
2020Apr,            4
2020Apr,            3
2021Jan,             3
2021Jan,             4
2021Jan,             5
2021Jan,             5
2021Feb,            2
2021Feb,            2
2021Feb,            1
2021Feb,            2
2021Mar,           4
2021Mar,           3
2021Mar,           1
2021Apr,            5
2021Apr,            1
2021Apr,            2
2022Jan,             1
2022Jan,             4
2022Jan,             6
2022Jan,             7
2022Feb,            3
2022Feb,            7
2022Feb,       5
2022Feb,            4
];

[juraj_misina · Accepted solution]

Hi. Try this

load
MonthYear,
Month(MonthYear) as Month1,
Year(MonthYear) as Year1,
sum(Unit) as Monthsum,
If(Year(MonthYear)=Peek(Year1), RangeSum(sum(Unit), Peek(Cumulative)), sum(Unit)) as Cumulative
Resident Dat2
group By MonthYear
Order By MonthYear;

Hope this helps.
Juraj

[nezuko_kamado]

The code caused an error, so I adjusted your code a bit, and which working great. Thank you so much!

CumulatedTable_Temp:
load
MonthYear,
Month(MonthYear) as Month1,
Year(MonthYear) as Year1,
sum(Unit) as Monthsum
Resident Dat2
group By MonthYear;

TABLE:
LOAD 
MonthYear,
Year1,
Month1,
If(Year(MonthYear)=Peek(Year1), RangeSum(Monthsum, Peek('Cumulative')), Monthsum) as Cumulative
RESIDENT CumulatedTable_Temp;

[juraj_misina]

Hi. I'm glad you got it working. However, I noticed you omitted "order by" which is important for Peek() to work as expected:

TABLE:
LOAD 
MonthYear,
Year1,
Month1,
If(Year(MonthYear)=Peek(Year1), RangeSum(Monthsum, Peek('Cumulative')), Monthsum) as Cumulative
RESIDENT CumulatedTable_Temp
ORDER BY MonthYear
;

[nezuko_kamado]

I have another question if there's any way I can assign the order of month?

Now, we're stacking Jan, Feb, Mar , Apr.. to Dec.  But , let just say,  if my fiscal year starts from Mar and finishes to Feb,  how can I assign the new order to Rangesum() in load script?

Thank you!

[juraj_misina]

You can use InYear() function (https://help.qlik.com/en-US/sense/May2022/Subsystems/Hub/Content/Sense_Hub/Scripting/DateAndTimeFunc...)

If(InYear(MonthYear, Peek(MonthYear), 0, 3), RangeSum(Monthsum, Peek('Cumulative')), Monthsum) as Cumulative