Fiscal Year Date Formula?
Author Message
Fiscal Year Date Formula?

Does anyone know how to create a formula against the system date that
will return a fiscal year value?  Example:  if the month equals sept,
oct, nov, or dec, +1 to system date.

Sat, 20 Oct 2001 03:00:00 GMT
Fiscal Year Date Formula?
http://www.seagatesoftware.com/kbase/

Returning all records from beginning of given fiscal year to
the last full month

Returning all records from beginning of given fiscal year to the last
full month

Product: Seagate Crystal Reports Pro      Version: 7.0.6.192      Applies
to: All versions
Database: N/A     Platform: N/A           Date Verified: 4/22/99

Problem:

?     Seagate Crystal Reports
?    Complex Formulas
?    Altering fiscal years for further calculations and comparisons

A report is required to return all records from the beginning of the
fiscal year (for example Sept.) to the last day of the month previous to
the current date.
That is, how can a fiscal year be altered to September through August,
and then be compared to values from September through the last full
month?

Solution:

?    Assuming this is 1999, the formula would be for the 1998-99
fiscal period only. Be aware that this formula must be changed next year
as CurrentDate's year will increase by one. So, where 1 is currently
subtracted from the year, 2 would be subtracted for next year, and for
the following year 3 would be subtracted, etc. This could be automated by
testing for the current year and changing the value based on that;
however, this would add a level of complexity.
?    Note:
To get this to work for records that occur on leap years, a test
would be needed, like
{date.field} in (1996,2000,2004),
and then the
Day(Date( Year(CurrentDate), Month (CurrentDate), 1) - 1))
portion would need to be adjusted accordingly. Simply change the
year involved to that of a leap year, and that would account for the
extra day at the end of February.

//The first part of the formula tests for January. Since month-1
is month 0 of the same year,
//the month needs to be reverted to December of the previous
year whenever the current
//month is January.

if month(CurrentDate) = 1 then
({Orders.Order Date} >= Date(1998,09,01) and {Orders.Order
Date}   Date(year(CurrentDate)-1, 12, Day(Date( Year(CurrentDate), Month
(CurrentDate), 1) - 1)) )
else ({Orders.Order Date} >= Date(1998,09,01) and {Orders.Order
Date}

Public:
RMA
C2003102
---------------------------------
KBASEID: C2003102

Quote:

> Does anyone know how to create a formula against the system date that
> will return a fiscal year value?  Example:  if the month equals sept,
> oct, nov, or dec, +1 to system date.

Sat, 20 Oct 2001 03:00:00 GMT
Fiscal Year Date Formula?
Thanks for sending that info.  I was unfamiliar with the technical website
and found it helpful for other problems, but not, unfortunately, for this
one.  I think I may have misstated my issue.  I need a formula to print the
fiscal year, not the values for fiscal year.  Meaning, I need the date to
print the system date for months Jan-Aug. and the system date + 1 for months
Sept-Dec.  How does one do a conditional date?
Quote:

> Here is a technote that might help you. If not, try
> http://www.seagatesoftware.com/kbase/

>             Returning all records from beginning of given fiscal year to
> the last full month

> Returning all records from beginning of given fiscal year to the last
> full month

> Product: Seagate Crystal Reports Pro      Version: 7.0.6.192      Applies
> to: All versions
> Database: N/A     Platform: N/A           Date Verified: 4/22/99

> Problem:

>     ?     Seagate Crystal Reports
>     ?    Complex Formulas
>     ?    Altering fiscal years for further calculations and comparisons

>  A report is required to return all records from the beginning of the
> fiscal year (for example Sept.) to the last day of the month previous to
> the current date.
>  That is, how can a fiscal year be altered to September through August,
> and then be compared to values from September through the last full
> month?

> Solution:

>     ?    Assuming this is 1999, the formula would be for the 1998-99
> fiscal period only. Be aware that this formula must be changed next year
> as CurrentDate's year will increase by one. So, where 1 is currently
> subtracted from the year, 2 would be subtracted for next year, and for
> the following year 3 would be subtracted, etc. This could be automated by
> testing for the current year and changing the value based on that;
> however, this would add a level of complexity.
>     ?    Note:
>          To get this to work for records that occur on leap years, a test
> would be needed, like
>          {date.field} in (1996,2000,2004),
>          and then the
>          Day(Date( Year(CurrentDate), Month (CurrentDate), 1) - 1))
>          portion would need to be adjusted accordingly. Simply change the
> year involved to that of a leap year, and that would account for the
> extra day at the end of February.

>          //The first part of the formula tests for January. Since month-1
> is month 0 of the same year,
>          //the month needs to be reverted to December of the previous
> year whenever the current
>          //month is January.

>          if month(CurrentDate) = 1 then
>          ({Orders.Order Date} >= Date(1998,09,01) and {Orders.Order
> Date}   Date(year(CurrentDate)-1, 12, Day(Date( Year(CurrentDate), Month
> (CurrentDate), 1) - 1)) )
>          else ({Orders.Order Date} >= Date(1998,09,01) and {Orders.Order
> Date}

> Public:
>  RMA
> C2003102
> ---------------------------------
>         KBASEID: C2003102

> > Does anyone know how to create a formula against the system date that
> > will return a fiscal year value?  Example:  if the month equals sept,
> > oct, nov, or dec, +1 to system date.

Sun, 21 Oct 2001 03:00:00 GMT
Fiscal Year Date Formula?
if Month (CurrentDate) = 9-12 then +1 else  LongYear

Quote:

> Thanks for sending that info.  I was unfamiliar with the technical website
> and found it helpful for other problems, but not, unfortunately, for this
> one.  I think I may have misstated my issue.  I need a formula to print the
> fiscal year, not the values for fiscal year.  Meaning, I need the date to
> print the system date for months Jan-Aug. and the system date + 1 for months
> Sept-Dec.  How does one do a conditional date?

> > Here is a technote that might help you. If not, try
> > http://www.seagatesoftware.com/kbase/

> >             Returning all records from beginning of given fiscal year to
> > the last full month

> > Returning all records from beginning of given fiscal year to the last
> > full month

> > Product: Seagate Crystal Reports Pro      Version: 7.0.6.192      Applies
> > to: All versions
> > Database: N/A     Platform: N/A           Date Verified: 4/22/99

> > Problem:

> >     ?     Seagate Crystal Reports
> >     ?    Complex Formulas
> >     ?    Altering fiscal years for further calculations and comparisons

> >  A report is required to return all records from the beginning of the
> > fiscal year (for example Sept.) to the last day of the month previous to
> > the current date.
> >  That is, how can a fiscal year be altered to September through August,
> > and then be compared to values from September through the last full
> > month?

> > Solution:

> >     ?    Assuming this is 1999, the formula would be for the 1998-99
> > fiscal period only. Be aware that this formula must be changed next year
> > as CurrentDate's year will increase by one. So, where 1 is currently
> > subtracted from the year, 2 would be subtracted for next year, and for
> > the following year 3 would be subtracted, etc. This could be automated by
> > testing for the current year and changing the value based on that;
> > however, this would add a level of complexity.
> >     ?    Note:
> >          To get this to work for records that occur on leap years, a test
> > would be needed, like
> >          {date.field} in (1996,2000,2004),
> >          and then the
> >          Day(Date( Year(CurrentDate), Month (CurrentDate), 1) - 1))
> >          portion would need to be adjusted accordingly. Simply change the
> > year involved to that of a leap year, and that would account for the
> > extra day at the end of February.

> >          //The first part of the formula tests for January. Since month-1
> > is month 0 of the same year,
> >          //the month needs to be reverted to December of the previous
> > year whenever the current
> >          //month is January.

> >          if month(CurrentDate) = 1 then
> >          ({Orders.Order Date} >= Date(1998,09,01) and {Orders.Order
> > Date}   Date(year(CurrentDate)-1, 12, Day(Date( Year(CurrentDate), Month
> > (CurrentDate), 1) - 1)) )
> >          else ({Orders.Order Date} >= Date(1998,09,01) and {Orders.Order
> > Date}

> > Public:
> >  RMA
> > C2003102
> > ---------------------------------
> >         KBASEID: C2003102

> > > Does anyone know how to create a formula against the system date that
> > > will return a fiscal year value?  Example:  if the month equals sept,
> > > oct, nov, or dec, +1 to system date.