Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Calculated starting date based on previous group ending date

Status
Not open for further replies.

Llazwas

IS-IT--Management
Feb 16, 2007
58
US
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!
 
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
 
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.

[yinyang] Madawc Williams (East Anglia, UK). Using Crystal 2008 with SQL and Windows XP [yinyang]
 
Thanks guys, I will try both and post the results
 
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!
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top