Date Last7Days

[EBee]

How come when I schedule a report and I use the Last7Days and run it on a Monday at 8:00 AM I don't get the Last Monday's value. Is there a better way, maybe formula that will allow me to grab items from Monday-Sunday every week.

thanks
erwin

 

[Nuffsaid]

Hi,

I think it's including the Monday you run the report on as one of the 7 days, Mon, Sun, Sat, Fri, Thur, Wens, Tues.

Try running your report late Sunday night.

Nuffsaid.

 

[EBee]

I tried to run the report sunday night and is still not including Sunday. What else should I try , I am about to change my prompt to just add the start date and end date. This will not allow me to schedule it

thanks for any help

erwin

 

[synapsevampire]

Try this in your record selection:

{MyTable.MyDateFied} >= datadate-7

Last 7 days would not include the previous Monday, that's the previous 8 days.

If you don't want the current Monday included, then alter the selection criteria to:

{MyTable.MyDateFied} >= datadate-7
and
{MyTable.MyDateFied} <= datadate-1

Also, I believe that historical instances of SI will use the current date to determine last7 days, not the date that the report was ran.

The formula above will always work.

-k

http://www.informeddatadecisions.com

kai@informeddatadecisions.com

 

[EBee]

I have a bit of a problem, the Date field is a number type, 1020523 = 5/23/02. I have formula called
@DATE = WriterDate({DAGENT.UDAY}) to show 5/23/02 on the report. Where should i put the conversion you suggested.

thanks
erwin

 

[synapsevampire]

If @date is a date type, then just use:

@DATE >= datadate-7
and
@DATE <= datadate-1

If not, convert it to a date using the date() function either in your @DATE formula, or on the fly in the record selection criteria.

To place the formula in the record selection criteria (select from the menu):

Report->Edit Selection Formula->Record

-k

http://www.informeddatadecisions.com

kai@informeddatadecisions.com

 

[goranm]

Hi !

Here is another way to get the records from last weeks
monday to sunday.
(independent of which day in the present week you run the report).

Use this in your record selection formula:
Date({your datefield}) >= (CurrentDate) - DayOfWeek(CurrentDate) -5 and
Date({your datefield}) <= (CurrentDate) - DayOfWeek(CurrentDate) +1

And if you want to show the period in the page header
just use the ToText-function like this:

ToText(CurrentDate - DayOfWeek(CurrentDate) -5) + ' to ' +
ToText(CurrentDate - DayOfWeek(CurrentDate) +1)

/Goran