CurrentDate and Find Last Business Date Formula

[DeniseAA]

I'm new to Crystal Reports and am having problems creating a formula that pulls records from the last business day.

The date field in the table I'm pulling from is formatted:
02/01/2002 03:02:11AM

The formula I tried to use is:
IF dayofweek (CurrentDate) = 1
Then CASE.swDateCreated = (CurrentDate - 2)
Else CASE.swDateCreated = (CurrentDate - 1)

The error I get is:
A number, currency amount, boolean, date, etc. expected.

Thanks in advance for any help.

DeniseAA

 

[Naith]

Denise,

You want to be assigning the "Currentdate -N" details to a variable using := rather than =.

e.g.

WhilePrintingRecords;
DateVar X;

If DayOfWeek(CurrentDate) = 1
Then X := (CurrentDate - 2)
Else X := (CurrentDate - 1);

Good luck,

Naith

 

[synapsevampire]

I'd limit the number of rows that you're returning in the record selection criteria (use Database->Edit Selection Formula->Record), rather than somehow trying to limit them within Crystal details in a formula, passing the SQL to the database will increase performance:

Your formula assumes that the report will never be ran on a Monday, dayofweek = 1 is a Sunday.

This should work.

Create a formula called GetLastBizDate as follows:

(
If dayofweek (CurrentDate) < 3
Then
date(maximum(lastfullweek)-1)
Else
(CurrentDate - 1)
)

Do NOT use whileprintingrecords and variables in a formula that will be referenced in the record selection criteria, this will almost always prevent the SQL from being passed to the database, thus degrading performence (and you can't use a whileprintingrecords in the record selection criteria directly).

Now go into the record selection criteria and enter:

{MyTable.MyDateField} = {@GetLastBizDate}

Do NOT try to use {@GetLastBizDate} code directly in the record selection criteria to save a step, Crystal won't pass the SQL if you do.

Should be fast and accurate for all days.

-k

http://www.informeddatadecisions.com

kai@informeddatadecisions.com

 

[DeniseAA]

Thanks to both Naith and synapsevampire.

I tried synapsevampire's suggestion but am getting a date-time is required error at maximum in the formula.

Should I have put in something else?

Thanks

 

[synapsevampire]

Sorry, use this (can ya tell I didn't test?):

(
If dayofweek (CurrentDate) < 3
Then
date(datetime(maximum(lastfullweek)-1) )
Else
(CurrentDate - 1)
)

This assumes that your field to compare is a date, not a datetime.

-k

http://www.informeddatadecisions.com

kai@informeddatadecisions.com

 

[DeniseAA]

Thanks again,

I am getting ready to try synap's code now, but in the meantime I have another question. Someone at work used the following code. I would like to understand how it works but she cannot it. This report is only run during the week.

If DayOfWeek(CurrentDate) = 2
and DateDiff(&quot;d&quot;, {SW_CMWGL_TASK.swStartDate}, CurrentDate) = 3
Then {rj_branch.rjSubsidiaryCode} = &quot;IMD&quot;
and {SW_SPECIALIST.swGroupName} = &quot;Order Admin&quot;
and {SW_CMWGL_TASK.swObjectType} = &quot;CHANGE&quot;


Else If DayOfWeek(CurrentDate) <> 2
and DateDiff(&quot;d&quot;, {SW_CMWGL_TASK.swStartDate}, CurrentDate) = 1
Then {rj_branch.rjSubsidiaryCode} = &quot;IMD&quot;
and {SW_SPECIALIST.swGroupName} = &quot;Order Admin&quot;
and {SW_CMWGL_TASK.swObjectType} = &quot;CHANGE&quot;

Denise

 

[Naith]

If DayOfWeek(CurrentDate) = 2
If today is Monday
and DateDiff(&quot;d&quot;, {SW_CMWGL_TASK.swStartDate},CurrentDate) = 3
and {SW_CMWGL_TASK.swStartDate} is 3 days before today
Then {rj_branch.rjSubsidiaryCode} = &quot;IMD&quot;
and {SW_SPECIALIST.swGroupName} = &quot;Order Admin&quot;
and {SW_CMWGL_TASK.swObjectType} = &quot;CHANGE&quot;

The second part of the formula says if it's not Monday, but {SW_CMWGL_TASK.swStartDate} was yesterday, then do the same thing.

Naith

 

[DeniseAA]

Thanks Naith,

I see where the statements are testing if it's Monday, but how does Crystal know to only pull records from the last business date? When I read this statement it looks to me like Crystal is going to pull all records where SubsidiaryCode = &quot;IMD&quot; and swGroupName = &quot;Order Admin&quot;
and swObjectType = &quot;CHANGE&quot;? Where does Crystal set Startdate to last business day.

I know just enough to get confused.
Thanks,
Denise

 

[Naith]

The first two lines of the formula are where you need to look.

If the report is run on a Monday, then it checks to see if your StartDate was last week Friday (&quot;3 days before today&quot; in my last post) - the last business day.

If the report is run on any day other than Monday, then it checks to see if your StartDate was yesterday - and being as this report is only run Monday to Friday, yesterday must be the last business day (holidays aside).

Naith