QTD & YTD Problem

[Myla944]

Good Morning,

I am using Crystal Reports version 9 and SQL Server 2000

I need to get the QTD and YTD information for my report. I have set up a parameter that reads the Week Ending Date (Saturday's Date )from {tbl.manufacturedbudget.EndDate} and it is working great for retrieving the information on a weekly basis. My problem is when I try to use the parameter date with the calendar1stqtr (etc.) it does not work. This function only seems to work if I use the currentdate. I have the same problem with the YTD function. I do not want to use the current date but to instead use my parameter date in case someone selects a year prior or 2 years prior. Is there a formula I can use instead of using the calendarqtr and YTD functions?

My report is a manual crosstab type of report.

My report looks like this:

WE Date WE Date WE Date WE Date QTR YTD
5/22/04 5/15/04

Regs 505,960 535,020 etc etc ? ?
Qtrs 141,780 145,690

Here is my 2 formula's that I am using the get the last 4 weeks of information:
We sell in Trays and I need to get the Dozens per Tray

if {tblBudgetManufactured.EndDate} = {?WE Date}
and {tblCompany.CompanyName} = {?Bakery}
then {tblBudgetManufactured.Qty}*{tblBudgetManufactured.EaCount}/12

then I use this one to sum the above formula to get the correct data:
sum ({@1st Wk Sum},{tblSKU.ShortDesc})

I tried to use this to get the QTD information and it does not work:

if {?WE Date} in Calendar2ndQtr
and {tblCompany.CompanyName} = {?Bakery}
then {tblBudgetManufactured.Qty}*{tblBudgetManufactured.EaCount}/12

and this:

sum ({@QTD},{tblSKU.ShortDesc})

and it does not return the correct information.

When I change my parameter {?WE Date) to tblBudgetManufactured.EndDate} it works but it only shows the current year information and if you change to a prior year it does not work. I do not understand what I am doing wrong, as my parameter works fine in the weekly columns for any year that is selected.

Could someone please help? I hope I explained my situation better in this post.

Sincerely,
Myla

 

[dtempleton]

Calendar2ndQtr is for the current year only. Maybe something like this work:

if {?WE Date} in [date(year({?WE Date}), 4, 1) to date(year({?WE Date}), 6, 30)] and {tblCompany.CompanyName} = {?Bakery}
then {tblBudgetManufactured.Qty}*{tblBudgetManufactured.EaCount}/12