Calculation using value on next row

[Dayna]

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

[Miguel_Angel_Baeyens]

Hello,

You can use peek with negative offsets to look starting at the end, i. e.:

peek('Field', -1, 'TableName')

would look for last value of Field in TableName or use functions before() and after() in pivot tables.

[Miguel_Angel_Baeyens]

I'd give a try as well, should your table use always same rder in columns to function Column(No.) where "No." is number of column, i. e.:

Sum(Sales) / Column(4)