Date Ranges - "Last4WeeksToSun" - Need last 8 weeks

[kmcclung]

Crystal 8 has a function under the date ranges to pull the last 4 weeks to Sunday. But I need the last 8 full weeks (without entering any parameters). For example today is 11/20, I need all values as far back as 9/16. Any suggestions would be helpful! Thanks in advance...

 

[csm2]

Look at DateSerial function. Playing with its arguments will give you almost any date range you need.

 

[dbielenda]

From Crystal Reports Help:

The following examples are applicable to both Basic and Crystal syntax:

DateSerial (1999, 6, 15)

DateSerial (2000, 1 - 7, 15)

DateSerial (1999, 1, 166)

All three return the date June 15, 1999. The second says that 7 months before January 1, 2000 is June 15, 1999. The third says that the 166th day of 1999 is June 15, 1999.

DateSerial (1996 + 12, 2 + 13, 29 + 14)

Returns April 12, 2009. What it means is that 12 years, 13 months and 14 days from February 29, 1996 is April 12, 2009.

 

[kmcclung]

Thanks but now I face another dilema. The DateSerial requires numbers to be entered as the arguments, but mine are dates.

I first have to determine the date of the Sun in the last full week. This is done with the following formula @LastSun:
minimum(LastFullWeek)
This returns 11/11/00.

Then I need to perform the DateSerial on that field returned by the @LastSun forumula above (which is a date) and get 2 months previous so:
DateSerial (@LastSun, @LastSun- 2, @LastSun)

But when I attempt this I get an error:
"A number is required here"

Any thoughts of how to get around this????

 

[csm2]

The arguments in DateSerial correspond to YEAR, MONTH and DAY in that order. Try the following:
DateSerial (Year (@LastSun),Month (@LastSun)-2,Day(@LastSun))

 

[FoxG]

Hi,

Last Sunday can be achieved with the formula...

currentdate - (dayofweek(currentdate)-1)

Then just subtract 7 * n to return n weeks before, eg.

{@last_sunday} - (7*8)

Hth,
Geoff

PS. If run on Sunday last Sunday will return current date !

 

[kmcclung]

I found a really easy solution....

local datevar LastSun := minimum(LastFullWeek);
LastSun - 56

The minimum(LastFullWeek) returns the date of Sunday in the last full week. Then I subtract 56 (8 weeks x 7 days/week) from that Sunday. This gives me the desired result: The Sunday 8 weeks ago from the Sunday of the last full week.

LastSun = 11/11/01 - 56 is 9/16/01

Thanks for everyone's help!

 

[alley211]

Another solution would be:

dateadd("ww", -8, {CurrentDate})

This subtracts 8 weeks from today's date.

Then you could ask for:

date in [dateadd("ww", -8, CurrentDate) to CurrentDate)]

 

[dgillz]

to further refine Alley's solution to restrict to Sunday cutoffs, use:

{Datefield} in Dateadd("ww",-8,minimum(LastFullWeek)) to Minimum(LastFullWeek)

the brackets [ ] are not needed in either formula Software Support for Sage Mas90, Macola, Crystal Reports and Goldmine