13 Replies Latest reply: Aug 8, 2014 4:39 AM by sujeet Singh

# Next row value

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)

DateOpening StockClosing Stock
26/03/1323445000
27/03/1350006780
28/03/1367809000
29/03/1390001000

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

• ###### Re: Next row value

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

• ###### Re: Next row value

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

Thanks

• ###### Re: Next row value

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

• ###### Re: Next row value

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

Thanks

• ###### Re: Next row value

Hi,

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

ClosingStock,

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

Date

FROM DataSource

ORDER BY Date;

Hope this helps you.

Regards,

Jagan.

• ###### Re: Next row value

...

FOR

i = 0 to NoOfRows('myTable')

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

.....

NEXT

hope it helps

• ###### Re: Next row value

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

• ###### Re: Next row value

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

• ###### Re: Next row value

Hi,

Do you want to generate Opening Stock from Closing Stock

or

Closing Stock from Opening Stock

Regards,

Kaushik Solanki

• ###### Re: Next row value

I want to generate the closing stock from the opening stock, so the closing stock figure will only be generated when the next days opening stock is inputted.

Thanks,

Ralph

• ###### Re: Next row value

Hi,

Try this.

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

Data1:
Load Date as Date1,Opening_Stock as Opening_Stock1,Previous(Opening_Stock) as ClosingStock
Resident Data Order by Date Desc;

Here because we are sorting the Date in descending order your previous function will act as next.

Regards,

Kaushik Solanki

• ###### Re: Next row value

Many thanks for this, it created the closing stock as expected.

• ###### Re: Next row value

A nice trick and concept of maths.