Cumulative sum for 3 next months at script level

[qlik4asif]

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

[LReeve]

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!

[qlik4asif]

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.

[LReeve]

Ah, missed the Product ID requirement!

Glad you got it working