Date Range Formula 1

[JSHoltsIT]

I have a date range formula

IF {V_Sales_History_w_Ecomm_Attributes.glpostdt} = {?Date Range}
THEN {@Shortened GL Date}

And I would like to create a second formula that leverages a condition of {?Date Range} minus one year.

My report pulls two sets of dates; a date range from this year and a the same range from last year.

How do I code the new formula to display info from last year's date range?

TIA

Jason

 

[Charliy]

{table.date} in Minimum({?Date Range})-365 to Maximum({?Date Range})-365

 

[JSHoltsIT]

I changed the -365 to -366 and the date yield is correct.

Thanks for the push.

JS

 

[chelseatech]

-366 will only work this year as it's a leap year. Will have a problem with that report when you run it in 2009.

I'd take this approach... Formula for Start date Last Year
local numbervar d:=day(minimum({?date Range});
Local numbervar m:=month(minimum ({?Date Range});
local numbervar y:=year(minimum({?Date Range});
Date(y-1,d,1)

Use a similar formula for End Date Last year by using a maxmimum function.

There is still a potential Leap Year day roll over if your date is ever supposed to be 29-Feb-2008. If you use that date this year you will want to use 28-Feb-2007, but when you use 28-Feb-2009, you will want the date to be 29-Feb-2008 for the previous year.

Have shown a solution for this in our newsletter earlier this year. You can download the back issue from our web site.

Editor and Publisher of Crystal Clear

http://www.chelseatech.co.nz/pubs.htm

 

[bdreed35]

Why not just this:

{table.date} in DateAdd("yyyy",-1,Minimum({?Date Range})) to DateAdd("yyyy",-1,Maximum({?Date Range}))

~Brian