Help on date range formulas 3

[Dragonscar]

I've browsed through the threads and so far havent' found an answer to my dilemma. I am trying to compare LastFullMonth this fiscal year with the same month last fiscal year and fiscal year to date through last month with last years fiscal year-to-date through the corresponding month.

Since our fiscal year begins June 1, I can't use the wonderful built ins. And I don't want to put in a specific date as this report will be used on the fly by users throughout the year. DateAdd requires a specific date and will not allow me to use a variable (e.g. Currentdate) or even a formula that uses a variable (e.g. {?date} using Currentdate.

Samples of what I've tried:
If {SOP30200.DOCDATE} in LastYearMTD - Month(1)
If {SOP30200.DOCDATE} in DateAdd ("m",-12, LastFullMonth)
Both of the above give an error message that a date is required.

If I simply change the date on the computer I still run into the problem with Last year MTD.

Any suggestions?

BTW, I did note that several people asked about changing the defaults for the builtins especially regarding fiscal years. Is there any way to do this so that this issue doesn't remain an issue for every report for businesses on a fiscal year? Especially for those that don't even make it easy by using an 6-month flip?

Thanks so much,
Laura

 

[bdreed35]

Try this in your Record Selection Criteria:

{SOP30200.DOCDATE} in lastfullmonth
or
{SOP30200.DOCDATE} in dateadd("yyyy",-1,minimum(lastfullmonth)) to dateadd("yyyy",-1,maximum(lastfullmonth))

~Brian

 

[lbass]

Try something like:

//{@lastfullmonth}:
if {table.date} in lastfullmonth then {table.amt}

//{@samemoastyr}:
if {table.date} in dateserial(year(currentdate)-1,month(currentdate-day(currentdate)),01) to dateserial(year(currentdate)-1,month(currentdate-day(currentdate))+1,01)-1 then {table.amt}

//{@thisyeartodate} (this assumes that fiscal year is named by the ending year, that you are using a parameter for fiscal year, and that the report is limited to the last two fiscal years):
if {table.date} in date({?fiscalyr}-1,07,01) to lastfullmonth then {table.amt}

//{@lastyeartodate}:
if {table.date} in date({?fiscalyr}-2,07,01) to dateserial(year(currentdate)-1,month(currentdate-day(currentdate))+1,01)-1 then {table.amt}

-LB

 

[Dragonscar]

Thanks for the tip, Brian. That works with the selection for the entire report, but then what do I use in the formula fields for the columns, which is where I've been having the problem?

The report is a sales history & comparison, e.g.:
Customer MTD LYMTD Variance YTD LYYTD Variance

Question 1: Using LastFullMonth in the formula for MTD gets me what I need, but since I can't use CurrentDate in the range and using LastFullMonth tells me I need a date, it won't work in YTD (for a fiscal year) (CDate(2004, 06, 01) to ____________)

Question 2: Here is my basic formula for MTD:
If {SOP30200.DOCDATE} in LastFullMonth then
if {SOP30200.SOPTYPE} = 4 then
{SOP30200.DOCAMNT} * -1 else
{SOP30200.DOCAMNT} * 1 else 0

Where I'm running into problems is replicating that for YTD, LYMTD, and LYYTD.

Thanks for your help.

Laura

 

[synapsevampire]

Dateadd does allow for built in dates:

dateadd("yyyy",-1,minimum(yeartodate))

It sounds like you're very close, so just understanding that you need to qualify and date range built in functions will get you there:

{table.date} >= dateadd("yyyy",-1,minimum(yeartodate))
and
{table.date} <= dateadd("yyyy",-1,maximum(yeartodate))

would qualify last years rows.

I could work out all of the formulas for you, but I suspect that you'll take this nugget and resolve everything.

Let's read if you need more assistance.

-k

 

[bdreed35]

I apologize for not fully reading your requirements in your first post.
The first step is to alter your Record Selection so that you just return the records that fall into those time frames:

//get fiscal year to date using June 1 as start of fiscal year
{SOP30200.DOCDATE} in
switch
(
    month(currentdate) = [6 to 12], cdate(year(currentdate),6,1) to CurrentDate,
    month(currentdate) = [1 to 5] , cdate(year(currentdate)-1,6,1) to CurrentDate
)
or
//get prioir fiscal year to date using June 1 as start of fiscal year
{SOP30200.DOCDATE} in
switch
(
    month(currentdate) = [6 to 12], cdate(year(currentdate)-1,6,1) to CurrentDate,
    month(currentdate) = [1 to 5] , cdate(year(currentdate)-2,6,1) to CurrentDate
)

Next, for your formulas, take a look at lbass's post. He has supplied some formulas that should get you on track.

I would point out in his post is that his this year to date formula, and last year to date formulas use July 1 as the start of the fiscal year and you wanted June 1. Just change 07 in the formulas to a 06.

Also, the Record Selection formula I gave you is all based off of current date, and his formulas are based off a parameter value that the user supplies. You will want to alter one of ours to match the others depending on what you want.

~Brian

 

[Dragonscar]

Okay, now my complete ignorance is going to show, but I can't get the (?Fiscalyr) parameter to be accespted as a date or date/time. When I put the date parameter into the formula, I just get the old "A number, currency amount, date, time, or date-time is required here." message.

 

[Dragonscar]

I think I may have solved the riddle. Please, guys, correct me if I've done something silly that may come back to bite me, but here are the formulas I'm using:

MTD: LastFullMonth
YTD: If {SOP30200.DOCDATE} in Cdate(2003,06,01) to dateadd("m",-12,maximum(lastfullmonth))...
LYMTD: if {SOP30200.DOCDATE} in dateserial(year(currentdate)-1,month(currentdate-day(currentdate)),01) to dateserial(year(currentdate)-1,month(currentdate-day(currentdate))+1,01)-1 ...
LYYTD: If {SOP30200.DOCDATE} in Cdate(2003,06,01) to dateadd("m",-12,maximum(lastfullmonth)) then ...

Obviously, I'm going to have to change the Cdates each year, but none of the variables was coming up with correct numbers from the data - probably just because I'm an idiot, but it does seem that they should be a little more parallel.

OTOH, I may be way off here. The numbers for MTD and LYMTD seem dead on. LYMTD comes very close, but the LYYTD seems further off.

Criminy! Are there any really good books on Crystal 8.5? The Help is so inadequate.

Thanks again for all your help.

Laura

 

[synapsevampire]

Naw, you can derive the years:

MTD: LastFullMonth
YTD: If {SOP30200.DOCDATE} in Cdate(year(currentdate)-1,06,01) to dateadd("m",-12,maximum(lastfullmonth))...
//don't use month-12, use:
YTD: If {SOP30200.DOCDATE} in Cdate(year(currentdate)-1,06,01) to dateadd("yyyy",-1,maximum(lastfullmonth))...

Anyway, sounds like you're on the way, good luck!

-k

 

[Dragonscar]

Thank you all for your help. I think I've finally got it working!

Laura

 

[Dragonscar]

Thank you all for your help. I think I've finally got it working!

Now if I can only figure out how to replicate the solution in other situations! <VBG>

Laura