Formula based on day value

[Joy1]

Good day

Please assist: CR9 and oracle database

I've created a report (based on calls logged) and want to run it daily (Mon to Friday only) for the previous day's data.

I've used this formula to determine day of week:
DayOfWeek ({tablename.log_date})

and this formula (@Logged) to try and execute the previous day's data:
If DayOfWeek ({tablename.log_date}) = 2 then
(CurrentDate - 3) else
If DayOfWeek ({tablename.log_date}) in 3 to 7 then
(CurrentDate - 1)

On Monday (day 2) I want to show friday's data, hence CurrentDate - 3

Record selection based on log_date:
Date({tablename.log_date}) = {@Logged}

I ran the report yesterday (24Jan) and it gave me data for the 23rd which was correct. When I ran the report again this morning (25Jan) it printed data for the 22nd and 24th instead of data for the 24th only.

Can anyone help me figure out what I might have done wrong?

Thanks in advance.


Joy

 

[bdreed35]

I think you want to base your logic on the day of week number of current date, not logged date.
Try it this way:

select DayOfWeek (CurrentDate)
case 1 : CurrentDate - 2
case 2 : CurrentDate - 3
default : CurrentDate - 1

~Brian

 

[synapsevampire]

The record selection should be something akin to:

If DayOfWeek(currentdate) = 2 then
{tablename.log_date} = CurrentDate - 3
else
{tablename.log_date} = CurrentDate - 1

-k

 

[Joy1]

Thanks guys

I tried this of Friday and waited to test it on Monday (today) and it sure does work.

I changed my DayOfWeek to be based on CurrentDate (instead of log_date) as Brian suggest and then applied the selection criteria as K suggested - works wonders. I even changed my system date to see if it works for the past previous days as well - works like a charm.

A zilion thanks

Joy