Day of Week Function in CR8.5

[dtrinite1]

Hello,
Inserted below is a formula that I have built using the DayofWeek function, which is not working in my Daily Apps report. The purpose of this formula is to capture daily loan applications for each business day of a week, avoiding the weekends (when no applications are taken).

IF (DayofWeek ({T_LN_STAT.STAT_AGGR_DT}) in 3 to 6)
THEN ({T_LN_STAT.STAT_AGGR_DT}-1)
else if (DayofWeek ({T_LN_STAT.STAT_AGGR_DT}) = 2)
then ({T_LN_STAT.STAT_AGGR_DT}-3)

-NOTE: initially I had used the Currentdate function in place of the actual DB field and that did not work either.

Can someone offer a better way to do this >?
Many thanks~!

DTrinite

 

[mbarron]

Please clarify what "not working" means. What is your data, what are your results? What were you expecting?

Currently your formula evaluates to see if the day is between Tuesday and Saturday and if it is it subtracts 1 day from the field. If it isn't between Tuesday and Saturday, it checks to see if it's a Monday. If it is, it subtracts 3 days and displays the previous Friday.

Is this what you want it to do?

Mike

 

[dtrinite1]

Mike,
Thanks for responding -- the report is looking to the previous business day for all loan applications taken on that day --when this formula is used, it returns -0- records, and I know from querying the database that there ARE records --

And, yes your evaluation is correct... if the 'STAT_AGGR_DT' is a Tues thru Sat, then subtract one day and get the previous day's records, else if that date field evaluates to be a Monday, then subtract 3 days and get the previous Friday's records. The formula editor sys there are no errors in that formula, but that doesn't necessarily mean it's right.

Any tips you can offer are appreciated --
DTrinite

 

[lbass]

I think you're looking for something like:

If DayofWeek(currentdate) in 3 to 6
then {T_LN_STAT.STAT_AGGR_DT} = currentdate-1 else
if DayofWeek(currentdate) = 2
then {T_LN_STAT.STAT_AGGR_DT} = currentdate-3

-LB

 

[dtrinite1]

Thanks, LB -- I had tried using the CurrentDate function a little differently (see below), then took it out of the formula because it still wasn't returning records.

IF (DayofWeek ({T_LN_STAT.STAT_AGGR_DT}) in 3 to 6)
THEN (CurrenDate-1)
else if (DayofWeek ({T_LN_STAT.STAT_AGGR_DT}) = 2)
then (currentDate-3)

I'll try your variation --it may work.
Thanks again --

 

[mbarron]

Is this formula your selection criteria or is it being used on the report canvas? The reason I ask is because if you use LB's formula it will result in a Boolean, not a specific date if it is placed on the canvas. Your formulas would both result in a specific date.

Mike

 

[dtrinite1]

Heloo Mike,
The formula is being used in the selection criteria, not the report canvas. I have tweaked it somewhat, and it does appear to be working, however, I cannot figure out how to grab Friday thru Sunday data when the report runs on a Monday. Below is what I am using now in the report.

IF (DayofWeek (currentdate) in 3 to 6) //Tues-Thurs
then (CurrentDate-1)
else if (DayofWeek (currentdate) = 2)
then (currentDate-3) // Monday, but only grabs the Friday data
Any ideas on how to grab Fri thru Sunday ?
Thanks for your feedback --
Debbie

 

[mbarron]

You can use "to" specify a range. It's the same as when you use the select expert and choose "between" from the drop down.


This should do it.

IF (DayofWeek (currentdate) in 3 to 6) //Tues-Thurs
then (CurrentDate-1)
else if (DayofWeek (currentdate) = 2)
then (currentDate-3) to (currentdate-1)

Mike

 

[dtrinite1]

Mike-- thanks for your feedback -- that range makes perfect sense. I'll plug it in on Monday and test it out--- Thanks again and have a great weekend!

Debbie

 

[dtrinite1]

---Well, unfortunately Crystal doesn't like the amendment you suggested (to use the CurrentDate function in a range)
---it returns this error message:
"the result of a formula cannot be a range"

so I'm back to the drawing board --

 

[mbarron]

That error sounds like you're trying to use it as a formula field and not in the select expert.

Please paste the formula you are using.

If it's in the select expert you should have a comparison field (in red below). I'm returning records where the {@date} is equal to currentdate-1:

IF (DayofWeek (currentdate) in 3 to 6) //Tues-Thurs
then {@date}=(CurrentDate-1)
else if (DayofWeek (currentdate) = 2)
then {@date} in (currentDate-3) to (currentdate-1)



Mike

 

[dtrinite1]

Mike -- this comparison works either a formula or a date field in the DB -- thanks a bunch for your feedback and assistance !

Debbie