Unusual MTD & YTD 2

[Johnsf]

Hi everyone,

Im trying to design a report where the financial month ends on the last Friday of every month and the financial year on the last Friday of November.

So for example March 2004 ended on 26th March 2004 and the Financial Year ended on 28 November 2003.

I need to calculate Month to Date (i.e. from 26/03 till today) and Year to Date (i.e. from 28/11/03 till today) figures

The formulas currently in use to set the month and year start are:

MTD - {?Run Date} - (Day({?Run Date}) - 1);
YTD - Date(Year(DateAdd("m", -6, {?Run Date})),7,1)

How do I change these so the month starts after the last friday of every month and the year starts after the last friday of november?

Thanks,

John

 

[Johnsf]

BTW the version of Crystal Im using is Enterprise 8.5

Cheers,

John

 

[-cush-]

To get dates after your physcal year, put this in your selection formula:

dateVar EndofNov := DateSerial (Year (CurrentDate)-1, 12, 1-1);

While DayOfWeek (EndofNov) <> 6 Do
(
EndofNov = DateAdd("d", -1, EndofNov)
);

{table.yourdate} > EndofNov

 

[-cush-]

I always think of an error right when I click post. Here is the revised version that considers whether you are past the last Friday of November:

dateVar EndofNov := DateSerial (Year (CurrentDate), 12, 1-1);

While DayOfWeek (EndofNov) <> 6 Do
(
EndofNov = DateAdd("d", -1, EndofNov)
);

if CurrentDate <= EndofNov then
(
dateVar EndofNov := DateSerial (Year (CurrentDate)-1, 12, 1-1);

While DayOfWeek (EndofNov) <> 6 Do
(
EndofNov = DateAdd("d", -1, EndofNov)
);

)

{table.yourdate} > EndofNov

 

[-cush-]

oops take out that second variable delaraion, but leave the assignment.

 

[Johnsf]

Oh my that goes right over my head!

On the report is a formula @YearStart which contains
Date(Year(DateAdd("m", -6, {?Run Date})),7,1)

So I just replace it with the code youve provided?

Thanks heaps,

John

 

[-cush-]

replace it with this:

dateVar EndofNov := DateSerial (Year (CurrentDate), 12, 1-1);

While DayOfWeek (EndofNov) <> 6 Do
(
EndofNov = DateAdd("d", -1, EndofNov)
);

if CurrentDate <= EndofNov then
(
EndofNov := DateSerial (Year (CurrentDate)-1, 12, 1-1);

While DayOfWeek (EndofNov) <> 6 Do
(
EndofNov = DateAdd("d", -1, EndofNov)
);

)

EndofNov

 

[Johnsf]

The @YearStart function is called from other code in the report like this:

if {TACCTRN.ATRNDTE} >= {@YearStart}

So I guess it returns the start of the year but not as per our requirements.

If I add the code you provided into the @YearStart function would that fix it? Because that code seems to be saving to a variable... How do I make it return the data in that variable when the function is called?

 

[Johnsf]

Sweet as Thanks. Do you have something similar that will fix the same thing for the month please?

Thanks,

John

 

[Johnsf]

Ummm that doesnt work.... only because the function doesnt seem to return EndOfNov when its called. I tried adding 'return EndOfNov' to it at the bottom but that doesnt work.

Is there a way to make the function return EndOfNov when called?

Thanks again!

 

[Johnsf]

I fixed that, however now it says:

A loop was evaluated more than the maximum number of times allowed.

 

[lbass]

The following should work for your year start formula {@yearstart-1} and represents the Friday before the Saturday year start:

if dayofweek(Dateadd("m", -month({?Date})-1, date(Year({?Date}),month({?Date})+1, 01)-1)) <> 6 then
Dateadd("m", -month({?Date})-1, date(Year({?Date}),month({?Date})+1, 01)-1)-dayofweek(Dateadd("m", -month({?Date})-1, date(Year({?Date}),month({?Date})+1, 01))) else
Dateadd("m", -month({?Date})-1, date(Year({?Date}),month({?Date})+1, 01)-1)-dayofweek(Dateadd("m", -month({?Date})-1, date(Year({?Date}),month({?Date})+1, 01)))+7

You could then use this in your record selection or create formulas like:

if {table.date} > {@yearstart} then {table.amt}

For a month to date formula, the following should work:

if dayofweek(date(year({?Date}),month({?Date})+1,01)-1
-dayofweek(date(year({?Date}),month({?Date})+1,01))) <> 6 then
(if {table.date} in date(year({?Date}),month({?Date}),01)-1
-dayofweek(date(year({?Date}),month({?Date}),01))+1
to date(year({?Date}),month({?Date})+1,01)-1-dayofweek(date(year({?Date}),month({?Date})+1,01)) then {table.amt}) else
(if {table.date} in date(year({?Date}),month({?Date}),01)-1
-dayofweek(date(year({?Date}),month({?Date}),01))+1
to date(year({?Date}),month({?Date})+1,01)-1-dayofweek(date(year({?Date}),month({?Date})+1,01))+7) then {table.amt}

-LB

 

[Johnsf]

Cool the YearToDate you provided now works fine in the @YearStart. Thanks.

Can I put the monthtodate into the @monthStart function too? Can you explain what either side of the 'else' does too please?

Cheers,

John

 

[lbass]

Just a note that I'm not sure that this is working quite right, and unfortunately I won't be able to look at it for a day or so, so maybe someone else will jump in to help refine this.

-LB

 

[-cush-]

Create a formula called @yearend:

dateVar EndofNov := DateSerial (Year (CurrentDate), 12, 1-1);//set to last day of nov this year
numberVar dayshift := dayofweek(EndofNov,crSaturday);

EndofNov = DateAdd("d", -dayshift, EndofNov); //move to the last friday

if CurrentDate <= EndofNov then //if we are before the last friday of nov then calc for last year
(
EndofNov := DateSerial (Year (CurrentDate)-1, 12, 1-1);//set to last day of nov this year
dayshift := dayofweek(EndofNov,crSaturday);
EndofNov = DateAdd("d", -dayshift, EndofNov); //move to the last friday
);

EndofNov

===========================

then in your record selection formula put:
{table.date} > {@yearend}

 

[-cush-]

Your month end formula would be simpler:

dateVar EndLastMonth := DateSerial (Year (CurrentDate), Month(CurrentDate), 1-1);//set to last day of last month
numberVar dayshift := dayofweek(EndLastMonth,crSaturday);

EndLastMonth = DateAdd("d", -dayshift, EndLastMonth); //move to the last friday

EndLastMonth

 

[Johnsf]

lbass the year to date you provided works perfectly. Ive checked that its returning 28 November 2003

 

[Johnsf]

kcushing your YTD works too Testing the MTD now but im pretty sure thats working fine too.....


Thanks very much guys!!!!!!!