In Row 1 we have the closing balance
In Row 2 we will be using the closing balance of Row 1 as opening balance + Purchase - consumption = Closing balance
In Row 3 the closing balance of Row 2 will become the opening balance and this will continue, untill a new item witn opening balance is provided.
If we use the get previous formula all the data get freesed in that column and we cannot recalculate the amount in the same column.
Do we have a method that replicates the method of cronological calculation as availabe in excel file. (Enclosing the method availabe in excel for reference)
Data of 31 March 2019 is the openign balance details available in another sheet which can be combined with another sheet using Append or Join in Idea.
Data available in raw file is Date, Time, Quantity and Amount. Rest all are calculations done which most of them can be done in idea also.
The issue lies with usign @getprevious formual which will get updated basis the previous rows clsting being added to the current row pourchase and consumption.
If anyone has any script to solve the issue be do share your experience and script for our reference.
b7shobhit
Offline
Last seen: 2 years 1 day ago
Joined: 07/25/2020 - 19:37
I have share the relevant columns that will be required for our analysis:
The opening records (Opening Quantity and Opening Amount) are from another sheet where we will probably use Append Function
Then the Purchase Quantity, Purchase Amount and Consumption Quantity is derived using if formula against each row.
Now comes the tricky part of Consumption Rate which is derived from the previous row closing rate.
Closing rate is derived as Closing Amount / Closing Quantity
Closing Quantity = Closing Quantity of Previous Row + Purchase Qty of current row + Consumption Qty of Current Row
Similarly Closing Amount = Closing Amount of Previous Row + Purchase Amount of current row + Consumption Amount of current row
And Consumption Amount is Consumption Quantity * Consumption Rate.
So Consumption Rate, Closing Quantity, Closing Amount and Closing Rate are all inter-related.
Please assist as to how to script in such situation.