Solved

# Calculating Date Ranges

Posted on 2010-01-04
762 Views
Hi,

I've built a report that needs to extract data in a date range for the last 8 weeks.  Now I have an issue when the new year rolls around.  My current formula is below.  I need the report to pick up data from 2 months ago, last month and last month the year before.  For example it's Jan 2010, I need to search on Dec 09, Nov 09 and Dec 08.

({Table.Field} in [dateSerial(year(CurrentDate) - 1, month(CurrentDate) - 2, 1) to dateSerial(year(CurrentDate) - 1, month(CurrentDate), day(currentdate) - 1)] or
{Table.Field} in [dateSerial(year(CurrentDate), month(CurrentDate) - 2, day(currentdate) -1) to dateSerial(year(CurrentDate), month(CurrentDate), day(currentdate) - 1)])
0
Question by:chris_desborough

LVL 34

Expert Comment

I'm not sure what you're looking for.  You mentioned the last 8 weeks, but then you threw "last month the year before" in there.

Are you looking for anything in the last 8 weeks (literally), ending today or yesterday (Which?), plus any date in the previous month, in the previous year (any date in Dec, 2008 in your example)?

FWIW, whatever you're looking for, I'd probably use the DateAdd function for at least part of it.  It gives you more control over the dates (I think).  For example, DateAdd ("ww", -8, CurrentDate) would give you the date 8 weeks before today (on 01/05, that would be 11/10).  But maybe that's just me.  I've never really used DateSerial, so I'm just not comfortable with it.

James
0

LVL 13

Expert Comment

Okay, breaking it down.  I'm doing this in basic syntax because that's what I'm familiar with.

Either you need to build a nasty looking selection formula, or a set of formulas. Here's the formula set.

@Currentstart
dim amonthago as date
dim currentstart as date
currentstart = datevalue(year(currentstart), month(currentstart), 01)
formula=currentstart

@currentend
dim amonthago as date
dim currentend as date
currentend = datevalue(year(currentend), month(currentend), 01)
formula=currentend

@lastyearstart
dim amonthago as date

dim lastyrstart as date

lastyrstart = datevalue(year(amonthago), month(amonthago), 01)
formula=lastyrstart

@lastyrend
dim amonthago as date
dim lastyrend as date
lastyrend = datevalue(year(lastyrend), month(lastyrend), 01)
formula=lastyrend

({Table.Field} in [{@currentstart} to {@currentend}] or
{Table.Field} in [{@lastyrstart} to {lastyrend}]
0

LVL 13

Expert Comment

And here's the selection formula, ready to go.

currentstart := datevalue(year(currentstart), month(currentstart), 01);
currentend := datevalue(year(currentend), month(currentend), 01);
datevar lastyrstart := datevalue(year(amonthago), month(amonthago), 01);
lastyrend := datevalue(year(lastyrend), month(lastyrend), 01);

({Table.Field}  in [currentstart to currentend]
or {Table.Field}  in [lastyrstart to lastyrend])
0

LVL 13

Accepted Solution

And in fact, using dateserial as in your current one, you can make the assumption that there are 12 months in a year....

({Table.Field} in [dateSerial(year(CurrentDate) , month(CurrentDate) - 14, 1) to dateSerial(year(CurrentDate) , month(CurrentDate)-12, day(currentdate) - 1)] or
{Table.Field} in [dateSerial(year(CurrentDate), month(CurrentDate) - 2, day(currentdate) -1) to dateSerial(year(CurrentDate), month(CurrentDate), day(currentdate) - 1)])

However, this only gives you the results you describe if run on the first day of the month (i.e. last month and the month before. )
0

LVL 16

Assisted Solution

(
({Table.Field} in dateadd("ww",-8,currentdate) to currentdate or
)
0

LVL 1

Author Closing Comment

Tried both examples accepted and both worked.  I'll keep both of these in my bag of tricks.  Thanks.
0

## Featured Post

Crystal Reports: 5 Tests for Top Performance It is complete, your masterpiece report.  Not only does it meet your customer’s expectations, it blows them out the water, all they want is beautifully summarised and displayed in a myriad of ways. …
Hot fix for .Net Crystal Reports 10.2.3600.0 to fix problems with sub reports running on 64 bit operating systems ISSUE: Reports which contain subreports fail with error "Missing Parameter Value" DEPLOYMENT SERVER OS: Windows 2008 with 64 bi…
It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.
This video gives you a great overview about bandwidth monitoring with SNMP and WMI with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're looking for how to monitor bandwidth using netflow or packet s…