Two Monthy Comparisons - One year apart

[socalvelo]

CR9
I am trying to compare crime data from the current last full month (say July 2007) to the same month last year (July 2006). I have no problem selecting the dates from last month, but I am trying to come up with a formula that will give me last year's month that corresponds with last month. Any suggestions?

 

[synapsevampire]

Use the dateadd functoin against your dates for last month, as in:

(
{table.date} >= {@MyStartDate}
and
{table.date} <= {@MyEndDate}
)
and
// here's last year:
(
{table.date} >= dateadd("yyyy",-1,{@MyStartDate})
and
{table.date} <= dateadd("yyyy",-1,{@MyEndDate})
)

You didn't post how you did last month, so I just based it on formulas. I suggest that you post your formulas in the future.

-k

 

[socalvelo]

Sorry about not putting the date formula in. I'm using fixed date ranges.

Record selection is
year({Reported_Date})=[2006,2007]



//@lastMonth
if {Reported_Date}in LastFullMonth
then 1
else 0

 

[synapsevampire]

Yeah, you need to change it all.

Record Selection:

(
{Reported_Date} in LastFullMonth
)
or
(
{Reported_Date} >= dateadd("yyyy", -1, minimum(LastFullMonth))
and
{Reported_Date} <= dateadd("yyyy", -1, maximum(LastFullMonth))
)

Now the criteria for last month in your formula:

{Reported_Date} in LastFullMonth

Now the criteria for last month for last year in your formula:

{Reported_Date} in dateadd("yyyy", -1, minimum(LastFullMonth))
to
{Reported_Date} <= dateadd("yyyy", -1, maximum(LastFullMonth))

-k

 

[socalvelo]

Synaps. Thank you for all your help.