topic Calculation of Opening & Closing Stock in App Development

Calculation of Opening & Closing Stock

Upali_Wijearatne — Sat, 24 Feb 2024 10:13:45 GMT

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.

Re: Calculation of Opening & Closing Stock

rubenmarin — Sat, 24 Feb 2024 16:34:23 GMT

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;

Re: Calculation of Opening & Closing Stock

Upali_Wijearatne — Sat, 24 Feb 2024 17:18:35 GMT

Thanks Friend. It works fine