Announcements
cancel
Showing results for
Did you mean:
Contributor III

## Calculation of Opening & Closing Stock

Dear  Experts

I am trying to build a data set for stock opening balances with below Data. I have opening Balance (Bal_BF) of the products and the  Purchases and Sales of the respective months. I want to add Purchases to Bal_BF and deduct the Sales and arrive at Closing Balance (Bal_CF).  Bal_CF of a particular is the Bal_BF of next month and I want to fulfil this in data load script please. Below shown is my data set and expected out put please.Kindly help me.

Labels (1)
• ### General Question

1 Solution

Accepted Solutions
MVP

Hi, you can use Peek() or Previous() to access previously loaded values, you can do a sorted load by PRD and Date and accumulae values from the previous row, like:

``````OriginalData:
Date,
PRD,
Bal_BF,
Purchase,
Sales
FROM [DataSource];

Balance:
Date,
PRD,
If(PRD=Peek(PRD)
,Peek(Bal_CF)
,Bal_BF
) as Bal_BF,
Purchase,
Sales,
If(PRD=Peek(PRD)
,RangeSum(Peek(Bal_CF),Purchase,-Sales)
,RangeSum(Bal_BF,Purchase,-Sales)
) as Bal_CF
Resident OriginalData
Order By PRD, Date;

DROP Table OriginalData;``````
2 Replies
MVP

Hi, you can use Peek() or Previous() to access previously loaded values, you can do a sorted load by PRD and Date and accumulae values from the previous row, like:

``````OriginalData:
Date,
PRD,
Bal_BF,
Purchase,
Sales
FROM [DataSource];

Balance:
Date,
PRD,
If(PRD=Peek(PRD)
,Peek(Bal_CF)
,Bal_BF
) as Bal_BF,
Purchase,
Sales,
If(PRD=Peek(PRD)
,RangeSum(Peek(Bal_CF),Purchase,-Sales)
,RangeSum(Bal_BF,Purchase,-Sales)
) as Bal_CF
Resident OriginalData
Order By PRD, Date;

DROP Table OriginalData;``````
Contributor III
Author

Thanks Friend. It works fine