Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello Guys,
Need to cumulative sum for next 3 months at back end
Sample Data
T1:
Load * Inline
[
Part,Month,Stock
P001,2019-01,100
P001,2019-02,200
P001,2019-03,100
P001,2019-04,400
P001,2019-05,100
P001,2019-06,200
P001,2019-07,400
P002,2019-01,100
P002,2019-02,200
P002,2019-03,100
P002,2019-04,400
P003,2019-01,100
P003,2019-02,200
P003,2019-03,400
P003,2019-30,600
];
Expected Result:
Part,Month,Stock,Cum_Sum
P001,2019-01,100,400
P001,2019-02,200,700
P001,2019-03,100,600
P001,2019-04,400,700
P001,2019-05,100,800
P001,2019-06,200,700
P001,2019-07,500,500
P002,2019-01,100,800
P002,2019-02,200,1100
P002,2019-03,500,900
P002,2019-04,400,400
P003,2019-01,100,700
P003,2019-02,200,1200
P003,2019-03,400,1000
P003,2019-30,600,600
Thanks in Advance
Hi @qlik4asif ,
I've had some success by using the peek function. There may be a cleaner way to do it, but this definitely seems to work for me!
T1tmp:
Load * Inline
[
Part,Month,Stock
P001,2019-01,100
P001,2019-02,200
P001,2019-03,100
P001,2019-04,400
P001,2019-05,100
P001,2019-06,200
P001,2019-07,400
P002,2019-01,100
P002,2019-02,200
P002,2019-03,100
P002,2019-04,400
P003,2019-01,100
P003,2019-02,200
P003,2019-03,400
P003,2019-30,600
];
NoConcatenate
T1:
Load
Part,
Month,
Stock,
Stock + PrevStock + Prev2Stock As CumSum;
Load
Part,
Month,
Stock,
If(IsNull(Peek(Stock, -1)), 0, Peek(Stock, -1)) As PrevStock,
If(IsNull(Peek(Stock, -2)), 0, Peek(Stock, -2)) As Prev2Stock
Resident T1tmp
Order By Part Desc, Month Desc;
Drop Table T1tmp;
Let me know if it works!
Hello @LReeve ,
It is working but, some of the vales are wrong as shown below.
I have changed the code slightly as below.
NoConcatenate
T1:
Load
Part,
Month,
Stock,
Stock + PrevStock + Prev2Stock As CumSum;
Load
Part,
Month,
Stock,
If(Part = Previous(Part) ,If(IsNull(Peek(Stock, -1)), 0, Peek(Stock, -1)),0) As PrevStock,
If(Part = Previous(Part) and Part = Previous(Previous(Part)),If(IsNull(Peek(Stock, -2)), 0, Peek(Stock, -2)),0) As Prev2Stock
Resident T1tmp
// Group By Part,Month
Order By Part Desc, Month Desc;
Drop Table T1tmp;
Now i am getting correct values.
Ah, missed the Product ID requirement!
Glad you got it working