get previous record

aamparo

Forums

Good evening, I'm trying to recreate a balance but it depends of some previous values, theres any way to get a value from the previous record with IDEAScript (i know there's a GetPreviousValue in the EquationEditor).
I will appreciate your help.
 
Thank you.

Brian Element Sun, 10/21/2018 - 15:34

Hi Binwell,

This is the script I came up with based on your excel spreadsheet.  It handles the scenarios that you gave me, if there are other variations the script won't hanlde those and will need to be modified.  Please validate the results before placing any reliance on them.

Thanks

Brian

b7shobhit Mon, 09/21/2020 - 05:33

Dear Brian,
We are trying to develop a routine on Stock Valuation, but the issue is that if we use the @Getprevious option we are unable to calculate the running inventory balance.
As you can see in the image, only 4 columns are from the original database, rest all are formula based.The issue is that the closing balance of the earlier row will be the opening of the next row and the closing balance formula for each row is Opening + Purchase +Consumption.
The quantity as at 31 March is the opening value, even if we keep the same in seprate row suing Join option in idea, we are unable to create the formula as available in excel. Similarly even by using append we are unable to creat the formula.
Requesitng you to please assist as to what can be done in such a situation.
 
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.

b7shobhit Sun, 09/20/2020 - 21:52

Dear Brian,
We are trying to develop a routine on Stock Valuation, but the issue is that if we use the @Getprevious option we are unable to calculate the running inventory balance.
As you can see in the image, only 4 columns are from the original database, rest all are formula based.
The issue is that the closing balance of the earlier row will be the opening of the next row and the closing balance formula for each row is Opening + Purchase +Consumption.
The quantity as at 31 March is the opening value, even if we keep the same in seprate row suing Join option in idea, we are unable to create the formula as available in excel. Similarly even by using append we are unable to creat the formula.
Requesitng you to please assist as to what can be done in such a situation.
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.