Quarterly rolling sum

[MarkiMark]

Hi, I would like to calculate rolling sum like in the table below. Data should be grouped by column "Family". For each quarter I want to take a look at current quarter and 2 quarters backwards. Any idea how to achieve this in script editor? I am not interested in set analysis using rangesum - above combination. Column "Quarter" comes from fiscal master calendar.

Thank you.

Family	Quarter	Qty	RollingSum3	Family,Quarter,Qty
A	FY2023Q2	10	10	A,FY2023Q2,10
A	FY2023Q3	20	30	A,FY2023Q3,20
A	FY2023Q4	30	60	A,FY2023Q4,30
A	FY2024Q1	40	90	A,FY2024Q1,40
A	FY2024Q2	50	120	A,FY2024Q2,50
A	FY2024Q3	60	150	A,FY2024Q3,60
A	FY2024Q4	70	180	A,FY2024Q4,70
B	FY2023Q2	5	5	B,FY2023Q2,5
B	FY2023Q3	10	15	B,FY2023Q3,10
B	FY2023Q4	15	30	B,FY2023Q4,15
B	FY2024Q1	20	45	B,FY2024Q1,20
B	FY2024Q2	25	60	B,FY2024Q2,25
B	FY2024Q3	30	75	B,FY2024Q3,30
B	FY2024Q4	35	90	B,FY2024Q4,35

[MATC · Accepted solution]

Hello,

You can try below solution:

tab:
Load * Inline [
Family; Quarter; Qty; RollingSum3; Family,Quarter,Qty
A; FY2023Q2; 10; 10; A,FY2023Q2,10
A; FY2023Q3; 20; 30; A,FY2023Q3,20
A; FY2023Q4; 30; 60; A,FY2023Q4,30
A; FY2024Q1; 40; 90; A,FY2024Q1,40
A; FY2024Q2; 50; 120;A,FY2024Q2,50
A; FY2024Q3; 60; 150;A,FY2024Q3,60
A; FY2024Q4; 70; 180;A,FY2024Q4,70
B; FY2023Q2; 5; 5; B,FY2023Q2,5
B; FY2023Q3; 10; 15; B,FY2023Q3,10
B; FY2023Q4; 15; 30; B,FY2023Q4,15
B; FY2024Q1; 20; 45; B,FY2024Q1,20
B; FY2024Q2; 25; 60; B,FY2024Q2,25
B; FY2024Q3; 30; 75; B,FY2024Q3,30
B; FY2024Q4; 35; 90; B,FY2024Q4,35
](delimiter is ';');

tab2:
load
Family,
Quarter,
Qty,
if(peek(Family)=Family and peek(Family,-2)=Family,peek(Qty)+peek(Qty,-2)+Qty) as Rolling3Qty
resident tab
order by Family;

drop table tab;

exit script;

[MarkiMark]

Thank you, MATC. It is working fine except first two quarters for each family. Any idea how to fix that?

[MATC]

It depends what you want to see in those nulls - since you do not have 3 qty to roll them.

Here is solution (maybe not perfect because im uncertain about performance but you can try to make it better) using Isnull() that on 1 family qty it has qty value on 2 family qty it has qty1+qty2 value and rest is the same. 

if(isnull(if(isnull(if(peek(Family)=Family and peek(Family,-2)=Family,peek(Qty)+peek(Qty,-2)+Qty))
,if(peek(Family)=Family,peek(Qty)+Qty)
,if(peek(Family)=Family and peek(Family,-2)=Family,peek(Qty)+peek(Qty,-2)+Qty))),Qty,if(isnull(if(peek(Family)=Family and peek(Family,-2)=Family,peek(Qty)+peek(Qty,-2)+Qty))
,if(peek(Family)=Family,peek(Qty)+Qty)
,if(peek(Family)=Family and peek(Family,-2)=Family,peek(Qty)+peek(Qty,-2)+Qty))) as Rolling3Qty

[MarkiMark]

It works well, thanks again.
In between I have combined your ideas with slightly different approach. Will test both solutions on larger dataset to compare performance.

tab2:
load
Family,
Quarter,
Qty
resident tab
order by Family
;
Drop table tab;

LEFT JOIN(tab2)
LOAD
Family,
Quarter,
IF(Family <> Previous(Family), 1, Peek('GroupRow')+1) AS GroupRow
Resident tab2
Order By Family, Quarter;

Tab3:
NoConcatenate
Load*,
if(GroupRow=1,Qty,If(GroupRow=2,Qty+Peek(Qty),Qty+Peek(Qty)+Peek(Qty,-2))) as Rolling3Qty
Resident tab2
;
Drop table tab2;

[WaltShpuntoff]

In your master calendar, create a sequence number for your quarters.

Something like

MasterCalendar:

load

....

<YearField>*4+<QuarterNumber> as QuarterSequence

At that point your current quarter is just a number, and the preceding quarters are just that number -1 and -2

Sequencing makes rolling periods much simpler.

HTH

ws

[MarkiMark]

Thank you, I will definitely use this some time. For my current task this is unfortunately not an option...