topic Re: Cumulative Sum in Expression in QlikView

Cumulative Sum in Expression

Wed, 25 Nov 2020 16:16:04 GMT

Line_ID	Rec_ID	Rec_Date	Qty	Pull_Date
SR_1212	1610	30-Mar-2016	3200	19-Mar-2016
SR_1212	1611	31-Mar-2016	3700	(1)???
SR_1212	1612	27-May-2016	3800	(2)???

Help Community!

Assuming I have a record as listed in the table above and I will like to calculate the Pull_Date for the unique Line_ID taking into consideration the different Rec_Date for each Rec_ID.

For Pull_Date (1), I want to be able to loop through the records, choose the Qty (3200) for the Rec_ID (1610) and use it to get my next Pull_Date (1) for Rec_ID (1611) and also do likewise for the second Pull_Date (2) of the Rec_ID (1612) using the Qty (3700) of the Rec_ID (1611) to get the Pull_Date (2).

This is making me go crazy. I hope you guys understand what I'm trying to do. Please, ask me any question, I can definitely explain further. Thanks guys!

Re: Cumulative Sum in Expression

swuehl — Wed, 30 Mar 2016 22:06:58 GMT

Sorry, I don't understand what you are doing with the Qty.

I understand you just want to fill in missing Pull_Dates, but how do you find Rec_ID 1611 based on Qty 3200 / Rec_ID 1610?

If you are just looking at the previous record, look into inter record functions like Peek() or Previous().

Re: Cumulative Sum in Expression

Anonymous — Thu, 31 Mar 2016 11:18:54 GMT

Please share the sample data and explain your problem clearly ....

Re: Cumulative Sum in Expression

Thu, 31 Mar 2016 13:56:54 GMT

Sorry guys! I'm not sure if the topic of the questions seems right for what I am trying to achieve but I hope I get a solution somehow. Thanks for your help and concern. I am attaching this file in excel to explain what I am trying to achieve. The Pull_Date cells are the calculated fields and they are calculated based on the Qty for each previous Rec_ID. Please, check out the excel file, hopefully that will help to clarify things and what I'm trying to achieve. Thanks.