Calculate a value based on a previous value 1

[sbf]

Hi Everyone, I need help
I have the following records in a table and want to calculate the last column “value”
The calculation is dependent of the change of ‘issuetype’ RECEIPT.

Item issuetype date quant u_price cur_bal value
0001 RECEIPT 01-09 12 20 12 12*20=240
0001 ISSUE 04-09 -5 7 7*20=140
0001 ISSUE 07-09 -2 5 5*20=100
0001 RECEIPT 08-09 8 25 13 8*25+((13-8)*20)=300
0001 ISSUE 09-09 -3 10 300/13*10 =231
0001 ISSUE 10-09 -2 8 300/13*8 = 185
0001 RECEIPT 11-09 6 20 14 14*20 = 280
0001 ISSUE 12-09 -4 10 10*20 = 200
0001 ISSUE 14-09 -1 9 9*20 =180

For the first 3 lines:
Value = u_prize*cur_bal

Line 4:
Issuetype = RECEIPT
u_price is greater than the previous u_price then
value = quant * u_prize + ((cur_bal – quant)* previous u_price)

line 5 and 6
And for the following lines where issuetype = ISSUE
value = previous value (where issuetype = RECEIPT)/previous cur_bal
(where issuetype = RECEIPT) * cur_bal

Line 7
Issuetype ‘RECEIPT’
u_price (20) is less than the previous u_price (25)
value = u_prize*cur_bal

Line 8 and 9
And for the following lines where issuetype = ISSUE.
value = u_prize*cur_bal

Does that make sense ?

I use am using CRW 8.5 / 9.
Thanks in advance, sbf-kla

 

[basil3legs]

Presumably you have a running total for the cur_bal (or just another field in your table). You need a formula to calculate the current price, e.g.

a formula called newprice as below:

IF not(isnull({sbf.u_price})) then
IF {sbf.issuetype} = "RECEIPT" and {sbf.u_price} > numbervar newprice then
numbervar newprice := (({sbf.quant}*{sbf.u_price})+(({#cur_bal}-{sbf.quant})*numbervar newprice))/{#cur_bal}
ELSE
numbervar newprice := {sbf.u_price};
newprice

this needs to go in the details section but can be suppressed. You will then need another formula which will just be the above formula * the current balance.

The price formula doesn't do exactly what you state but you are unclear to exactly how u_price works. e.g. in your example, after the second receipt, the price is 23.08. When the next one is 20, does the price used change to this as it is below 25 or below 23.08 and hence what would happen if it was 24?

The formula I have given can quite easily be edited accordingly but you will need to store another variable giving the last value of u_price if it is needed for comparison rather than the current price.

 

[sbf]

Just perfect - - Thanks you for your reply basil3legs.
Yes, cur_bal is a running total of the quant field.
The formula gives the right value on this example, but it is not a god example. Yes, it is unclear what happens if the u_price is 24 of instead of 20 in line 7, and I will try explain:

When we receive a new shipment the unit price is compared to the unit price of the last shipment.
If the unit price of the new shipment is higher, the current price should be:
(new quantity * new unitprice + (current quantity-new quantity) * last unitprice
If the unit price of the new shipment is lower than the unit price of the last shipment the current price should be:
current quantity * new unitprice

Item issuetype date quant u_price cur_bal value Cur_price
0001 RECEIPT 01-09 12 20 12 12*20=240 20
0001 ISSUE 04-09 -5 7 7*20=140 20
0001 ISSUE 07-09 -2 5 5*20=100 20
0001 RECEIPT 08-09 8 25 13 8*25+((13-8)*20)=300 23.08
0001 ISSUE 09-09 -3 10 300/13*10 =231 23.08
0001 ISSUE 10-09 -2 8 300/13*8 = 185 23.08
0001 RECEIPT 11-09 6 24 14 14*24 = 336 24
0001 ISSUE 12-09 -4 10 10*24 = 240 24
0001 ISSUE 14-09 -1 9 9*24 = 216 24

I edited your formula and compared to the last value of u_price, as you described, and the result was perfect!
Again. Thanks for the help.... sbf-kla