Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
look af "Above" and "Below" functions in the guite they fit ...
Above is an option although ideally I would like to calculate this in the script rather than a chart.
Thanks
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
Can you provide a simple example for the above as I don't think I've used a loop before.
Thanks
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
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
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.
...
FOR
i = 0 to NoOfRows('myTable')
LET var= Trim(Peek('myField', $(i), 'myTable'));
.....
NEXT
hope it helps
Hi,
Do you want to generate Opening Stock from Closing Stock
or
Closing Stock from Opening Stock
Regards,
Kaushik Solanki