Calculation using value on next row

Dayna · ‎2010-01-11

Hello,

I have a calculation within my script that works out the stock difference from today to a specific date:

[Daily Balance] - Cumul AS Balance;

My problem that I have encountered, is that the figure I need to deduct to calculation my balance, is the next figure... i.e.

BOM Chart - Date	Balance	Change	BOM Chart - Cumul
07/12/2009	311580	0.00	-121440
09/12/2009	311580	-21600.00	-121440
11/12/2009	289980	-21600.00	-99840
16/12/2009	268380	0.00	-78240
20/12/2009	268380	0.00	-78240
22/12/2009	268380	0.00	-78240
27/12/2009	268380	0.00	-78240

The Cumul is always one behind, as for the 11/12/2009 instead of 289980 it should be 268380. I was wondering if there was an opposite to Peek, so I can use the next figure... Or what you think is best?

Your help would be most appreciated!!!

Kind Regards,
Dayna

prieper · ‎2010-01-11

Easiest would be to reload the table in DESC-sortorder and then to use the PEEK-function.

HTH
Peter

Report Inappropriate Content · ‎2010-01-11

What about something like Peek('field',RowNo(),'table')?

Since peek expects a 0 for the first row, you don't have to add 1 to the RowNo() to get the next row after the current one.

I haven't tried this myself

prieper · ‎2010-01-11

Do not think that there is much of a difference, if you look ahead by your method or look one record back in a table, which is sorted in a descending order. Might be worth to give it a try...

Peter

Report Inappropriate Content · ‎2010-01-11

Have you tried using the "below" function? I use the "above" function to retrieve the prior months figure so that I can compare it to the current months figure.