|
Return to Newsletter Contents...
Turning Back Time
by: Aaron Zecman, Consultant, CRCP, MSCE
I have had plenty of students ask how
they can turn back time and it’s definitely not by playing a Cher CD or hearing
about Marty McFly’s adventures. It
is going back and pulling certain data for a specific time period based on the
current date (or parameter). In this
article I will start with the easiest of year, then move onto month and week.
In all of my examples, I am going to
use a fictitious date of {Hire Date} and using the CurrentDate function that is
built in Crystal (the current date
could be replaced by a parameter).
Also, all of my coding will go in as part of the record selection formula.
Let us start with year and imagine the
CurrentDate is 2/1/2008.
Year({HireDate}) = Year(CurrentDate) –
1
= 2008 – 1
= 2007
The Year of the CurrentDate would end
up being 2007 getting us all the {HireDates} from the previous year.
Now let us move onto the month.
This one will have to work a little bit differently because we have to
take the month into consideration (if the month is January (01) we cannot
necessarily subtract 1 like we did for the year because there is no month 0).
In order to fix this and solve we will use the DateAdd function that Crystal makes available for us.
Let us imagine that the CurrentDate is 1/14/2008.
Year({HireDate}) = Year(dateadd(‘m’,-1,CurrentDate))
and
Month({HireDate}) = Month(dateadd(‘m’,-1,CurrentDate))
=Year(12/14/2007) = 2007
=Month(12/14/2007) = 12
Now finally depending how your weeks
are calculated you could have a couple of different selection formulas.
We will use the assumption that the week begins on a Sunday and that
January 1 is the start of the first week for January and contains no December
dates. Let us imagine that the
CurrentDate is 1/3/2003.
DatePart(‘ww’,{HireDate}) = DatePart(‘ww’,dateadd(‘ww’,-1,CurrentDate))
and
Year({HireDate}) = Year(dateadd(‘ww’,-1,CurrentDate))
= DatePart(12/27/2002) = 52
= Year(12/27/2002) = 2002
Go to Top |
Return to Newsletter Contents
|
