Next row value

[#1]

Hello,

I have a spreadsheet which requires the next row of data so that I can calculate the closing stock of the day before (i.e. users only input the opening stock per day and thus I need to look at the next days opening stock to calculate the closing stock of the previous day)

Date	Opening Stock	Closing Stock
26/03/13	2344	5000
27/03/13	5000	6780
28/03/13	6780	9000
29/03/13	9000	1000

So for the above example for the 27/03/13 I currently only have the opening stock of 5000 inputted and I need to look at the 28/03/13 opening stock in order to complete the closing stock for 27/03/13. Obviously peek could be used if I was looking for the previous value but I'm not sure as to how to look at the next field value.

Thanks,

Ralph

[alexandros17]

look af "Above" and "Below" functions in the guite they fit ...

[#3]

Above is an option although ideally I would like to calculate this in the script rather than a chart.

Thanks

[alexandros17]

you can use peek even for new row ..

if you have a loop

for i= 0 to rowno()

     let val=peek('field', $(i)+1, 'mytable'

next

where $(i)+1 is next row

[#5]

Can you provide a simple example for the above as I don't think I've used a loop before.

Thanks

[kaushiknsolanki]

Hi,

     Try this script

Data:
Load Date(Date#(Date,'DD/MM/YYYY')) as Date,ClosingStock inline [
Date, ClosingStock
26/03/13,5000
27/03/13,6780
28/03/13,9000
29/03/13,1000
];

Data1:
Load Date as Date1,ClosingStock as ClosingStock1,Previous(ClosingStock) as OpeningStock
Resident Data Order by Date Asc;

Regards,

Kaushik Solanki

[#7]

Hi Kaushik,

I'm trying to generate the next field value and not the previous value, and it would be based on the opening stock value rather than the closing.

Thanks,

Ralph

[jagan]

Hi,

Try Previous() in script to get previous record field value,

LOAD

     ClosingStock,

     If(RowNo() = 1, OpeningStock, Previous(ClosingStock)) AS OpeningStock

     Date

FROM DataSource

ORDER BY Date;

Hope this helps you.

Regards,

Jagan.

[alexandros17]

...

FOR

i = 0 to NoOfRows('myTable')

LET var= Trim(Peek('myField', $(i), 'myTable'));

.....

NEXT

hope it helps

[kaushiknsolanki]

Hi,

    Do you want to generate Opening Stock from Closing Stock

     or

     Closing Stock from Opening Stock

Regards,

Kaushik Solanki