How to find previous and next record from a table?

[#1]

Hi....

Suppose I have a tbale with dates..

Date
01/01/2011
01/01/2011
01/01/2011
5/05/2011
8/8/2011
8/8/2011
10/10/2011

I want a table with following columns...

Date	Previous Date	Next Date
01/01/2011	-	01/01/2011
01/01/2011	01/01/2011	01/01/2011
01/01/2011	01/01/2011	5/01/2011
5/05/2011	01/01/2011	8/8/2011
8/8/2011	5/05/2011	8/8/2011
8/8/2011	8/8/2011	10/10/2011
10/10/2011	8/8/2011	-

I want Previous and next record of each rows.

Thanks in advance...

Satish

[swuehl]

I think you can use the interrecord functions: above and below:

=above(Date)

resp.

=below(Date)

as expressions for Previous Date and Next Date with dimension Date.

Regards,

Stefan

[#3]

I have to use those Previous Date and Next Date fields in Some calculations.

So I need those fields only in script part. I also used Previous and peek functions.

But value I am getting is not right.

Regards,

Satish.

[its_anandrjs]

Hi,

Yes right you need to use the Above and Below fuctions for the date view for the Previous Date and Next Date put Above(Date) , Below(Date).

See the attached sample for clearing your problems.

Regards

Anand

[swuehl]

If you want to create that table in script, you might use something like:

Left Join Load *,

peek(Date) as NextDate

resident Dates order by Date desc;

Left Join Load *,

peek(Date) as PreviousDate

resident Dates order by Date ASC;

Where Dates is your calendar table with field Date.