Calculation of Opening & Closing Stock

[Upali_Wijearatne]

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.

[rubenmarin · Accepted solution]

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:
LOAD 
  Date, 
  PRD, 
  Bal_BF, 
  Purchase, 
  Sales
FROM [DataSource];

Balance:
LOAD
  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;

[Upali_Wijearatne]

Thanks Friend. It works fine