Calculated starting date based on previous group ending date

[Llazwas]

Cyrstal 10 with a ProvideX database.

I started trying to write a sales report that uses a fiscal calendar to group and filter records but already running in to a problem. My fiscal calendar resides in a table (GL_FiscalYearDetail) with the following fields:

FiscalYear
FiscalPeriod
PeriodEndingDate

What I need to be able to do is calculate the next period's beginning date based on the prior period's ending date. I tried grouping by Fiscal Year, then Fiscal Period and thought I could create a formula based on Previous(GroupName ({GL_FiscalYearDetail.FiscalPeriod}) but getting a "This field has no previous or next value" error.

Not sure what to try next. Any help is greatly appreciated!

 

[IanWaterman]

I think Previous() only works with Details

Even that will run into NULLs for first record which does not have a previous, try adding the condition below to your formula

If not onfirstrecord then
...
else
....

Ian

 

[Madawc]

You could use variables to accumulate a value for one group and have it available for the next group.

Use SEARCH if you need instructions on using variables.

Madawc Williams (East Anglia, UK). Using Crystal 2008 with SQL and Windows XP

 

[Llazwas]

Thanks guys, I will try both and post the results

 

[Llazwas]

I wound up removing the groups and getting the beginning date from the details section with formula:

@PeriodBeginningDate
if not onfirstrecord then previous({GL_FiscalYearDetail.PeriodEndingDate})+1 else DateValue (1900,01,01)

I should be able to use this to filter the rest of my report. Thanks alot for all of your help!