Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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 |
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;
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;
Thank you, MATC. It is working fine except first two quarters for each family. Any idea how to fix that?
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
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;
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
Thank you, I will definitely use this some time. For my current task this is unfortunately not an option...