Prior Fiscal YTD Figures

[mrtanner]

Hi Guys,
I have checked out all the threads to do with this and nothing seems to work. I have downloaded Ken Hamadys formulas and have been using them but can't get this to work. I have created formulas for fiscal year and current fiscal year. The formula for YTD works beautifully but when I use the prior year formula:

Fiscal YTD:-

if {@Fiscal Year} = {@Current Fiscal Year}
and {NMEMO.DISPATCHED} <= Currentdate
then {NMEMO.FEES}
else 0

Works great...

Prior Fiscal YTD

if {@Fiscal Year} = {@Current Fiscal Year}-1
and {NMEMO.DISPATCHED} <= DateAdd ('yyyy' ,-1,CurrentDate)
then {NMEMO.FEES}
else 0

I get a 0 result.

I am using Crystal XI and an Oracle Database..

Any help would be greatly appreciated. I am not an experienced user but have been at this for 2 days now and its soooooo frustrating..Especially as I know someone there will see the mistake straight away ( I hope)

Thanks

Mr Tanner

 

[lbass]

What we can see looks fine, but we don't know the content of {@Fiscal Year} and {@CurrentFiscalYear}. Please share.

-LB

 

[mrtanner]

Thanks lbass. Forgot to put these in.

Here they are-

Fiscal Year:

Year ({NMEMO.DISPATCHED}+184)

Current Fiscal Year:

Year (Currentdate + 184)

Our Fiscal Year is from 1st July to 30 June.

Thanks again

Mr Tanner

 

[lbass]

All looks correct to me. Where are you placing this formula? If you want to see results for the fiscal year, you would need to place these formulas in the detail section and then insert sums on them at the fiscal year group level. The formulas would return zeros for detail records that don't meet the criteria and for the fiscal years to which they do not apply.

-LB

 

[mrtanner]

lbass,

Yes I am putting the formulas in details and then summing them in the report footer. The only thing I can see that's different from Ken Hamadys formula is that when I browse the data for @fiscal year, all years show where as in mine the only year I get is 2008?
This is his formula which looks the same as mine??

if Month ({Orders.Order Date}) >= 10
then Year ({Orders.Order Date}) + 1
else Year ({Orders.Order Date})

Any other ideas?

Mr Tanner

 

[lbass]

What is your record selection formula? Please paste it here.

-LB

 

[mrtanner]

lbass,

I don't have a record selection.
Here is the info from Ken's tips which as far as I can see says that there is no record selection for this and there isn't in his example and should just give info for the previous year to current date:

69. Calculating fiscal YTD (Report 67)
The Crystal Date Range Functions for WeektoDate and MonthToDate work fine in both calendar
and fiscal year environments, but the YearToDate and LastYearYTD are strictly for calendar
years. If you have to do these 2 functions for a Fiscal year you need to write at least 3
formulas. The first 2 use the Fiscal Year formula options mentioned in article 67.

First calculate the transaction Fiscal Year. I will use the second option described in article 67:

Year ( {Transaction.Date} + 92 )

Second define and calculate the current Fiscal Year. It could be calculated automatically using
functions like CurrentDate the maximum of LastFullMonth (See article 62). Or you could create
a parameter and let the user select the ending date of the report’s Fiscal YTD period. While a
parameter requires user input, it also gives the user the flexibility to run the report for prior
periods. Whichever method you use, the formula to determine the current fiscal year uses the
options in Article 67. For example, if you are using CurrentDate, so that today’s date is
automatically the end of your Fiscal YTD period, the formula would be:


Year ( CurrentDate + 92 )

Then you need the formulas that allows you to Count or Sum the Current Fiscal Year’s
transactions. To calculate the charges for the current Fiscal YTD your formula could be:

if {@Fiscal Year} = {@Current Fiscal Year}
and {Transaction.Date} <= CurrentDate
then {Transaction.Charges}
else 0

You would SUM this formula to calculate total charges for the current Fiscal YTD period. If
instead you need to determine how many of the records in the report were in "Fiscal YTD" you
would create a similar formula. But, instead of the Charges field you would use the number 1.
You would still do a SUM of the formula and that SUM would tell you how many records were
in the specified period.



To do a Prior Fiscal YTD you would use a similar formula. You would have to subtract one
from {@Current Fiscal Year} and also subtract one year from the CurrentDate (or your
parameter). The formula would look something like this:

if {@Fiscal Year} = {@Current Fiscal Year} - 1
and {Transaction.Date} <= DateAdd ( ‘yyyy’ , -1 , CurrentDate )
then {Transaction.Charges}
else 0

You would SUM this formula to calculate total charges for the Prior Fiscal YTD period. Again
you would use the number 1 as your THEN value to get the number of records.

Mr Tanner

 

[lbass]

I am familiar with this. I don't think there is anything wrong with your formulas. Please place the date field in the detail section and insert a minimum on it and then report back with the result.

-LB

 

[mrtanner]

Ok, I got a result. I used the record selection ( I guess thats what you meant) of greater than or equal to 01/07/2005 since that is the earliest data we have. The total doesn't equal the total shown in our reports but at least we have data.
Was the record selection what you wanted me to do??

Mr Tanner

 

[lbass]

No, I don't want you to do any record selection. I wanted you to place the date field in the detail section and insert a minimum on it so we could see whether there was data earlier than the current fiscal year.

-LB

 

[mrtanner]

Sorry, I misunderstood. Did what you asked and this was the result:

21/05/2003 12:00:00AM

Mr Tanner

 

[lbass]

This is really hard to troubleshoot. Please place the two formulas in the detail section along with the date field, and see if they are executing correctly based on the dates. Please show a sample of any unusual results.

-LB

 

[mrtanner]

Mate,
It seems to be working now...Don't know why but I'm not complaining..Thanks for all your time and help with this problem.It really is appreciated..

Mr Tanner

 

[mrtanner]

Lb,

One last question...Would I use the same basic formulas to do MTD, and MTD last year??

Mr Tanner

 

[mrtanner]

Sorry, I can do the MTD and MTD last year from the builtin formulas, but how would I do MTD 2 years ago??

Mr Tanner

 

[lbass]

How are you doing MTD last year?

-LB

 

[mrtanner]

Formula as follows:-

if {NMEMO.DISPATCHED}= LastYearMTD
then {NMEMO.FEES}
else 0

Mr Tanner

 

[lbass]

I think you could use this:

if {@Fiscal Year} = {@Current Fiscal Year}-2 and
{NMEMO.DISPATCHED} <= DateAdd ('yyyy' ,-2,CurrentDate) and
month({NMEMO.DISPATCHED}) = month(currentdate) then
{NMEMO.FEES} else
0

-LB

 

[mrtanner]

Lb,

Works perfectly.. Thanks again for all your help.

Mr Tanner