QTR & YTD - REALLY NEED HELP 2

[Myla944]

Crystal Version 9.0 & SQL Server 2000

Good Afternoon,

I have posted this before and did not explain this right, hopefully this time someone can help, ahhhh!

I need to show QTR and YTD information on my report, based on the Parameter date that is selected. My parameter is based on the weekending date (which shows a Saturday date in the database). I created formulas to show the weekly that is selected based on the parameter and it works.

When I try using the parameter {?WEDate} in my formula it show the incorrect information and will not work. Is there a way that I can create a formula that is based on the year only so if the user selects the current year or a prior year that will show the YTD totals?

I also want to create a formula to show QTD totals based on the parameter WE Date or at least some kind of formula that will show what the qtrly total was for the date that is selected in the parameter. I am totall stumped on this one. Please help!

This is the formula that I tried for the YTD and it does not work!
if {?WE Date} IN YEARTODATE
and {tblCompany.CompanyName} = {?Bakery}
then {tblBudgetManufactured.Qty}*{tblBudgetManufactured.EaCount}/12
else if {?WE Date} IN YEARTODATE - 1
and {tblCompany.CompanyName} = {?Bakery}
then {tblBudgetManufactured.Qty}*{tblBudgetManufactured.EaCount}/12

I have not come up with anything for the QTD. I tried using the Crystal CalendarQTR's but this does not work either.

Sincerely,

Myla

 

[synapsevampire]

It's generally best to post example data and expected output rather than trying to describe what each looks like.

You've given no information about the data nor what you intend to do with it. You've provided a lot of information about things that don't work, which doesn't help us understand your needs, it helps us understand what you don't need.

To limit rows in the report to the current year, you might use a parameter in this fashion:

Report->Edit Selection Formula->Record

{Table.field} >= {?dateparm}

When I create parameters for different date ranges, such as Yearly, Quarterly, Monthly, Last Year, Last Quarter, etc., I create a parameter to state exactly that, not a date parameter:

then I create 2 date formulas to determine the starting and ending dates for use in the record selection formula

@startdate
If {?dateparm} = "This Year" then
cdate(year(currentdate),month(currentdate),1)
else
If {?dateparm} = "This Quarter" then
...

Then build an enddate formula, and base the record selection off of those dates:

{Table.field} >= @startdate
and
{Table.field} <= @enddate

As for knowing which dates to use, here's an example for the last quarter for each formula:

@startdate
If {?Predefined Date Range} = "Last Quarter" Then
If (Month(CurrentDate) < 4) Then
DateTime(Year(CurrentDate)-1,10,1,0,0,0)
Else
DateTime(Year(CurrentDate),3*Truncate((Month(CurrentDate)+2)/3)-5,1,0,0,0)

@enddate
If {?Predefined Date Range} = "Last Quarter" Then
If Month (CurrentDate) < 4 Then
DateTime(Year(CurrentDate)-1,12,1,23,59,59)
Else
DateTime(Year(CurrentDate),3*Truncate((Month(CurrentDate)-1)/3)+1,1,23,59,59)-1

-k

 

[Myla944]

Good Morning,

Thank you Synapsevampire, seeing what you wrote has let me see that I have been approaching my problem in a totally incorrect manner. I shall try what you have suggested and see how it works. Here is what I have thus far on my report since I did not show all the information needed (sorry about that):

WEDate WEDate WEDate QTD YTD
5/29/04 5/22/04 5/15/04 2004 2004

Regs 303,105 250,800 475,800 0 0
Qtrs 150,000 etc etc

I created a parameter for the user to select the End Date which is the Week Ending Date above and when selected it shows the correct information for the amount of dozens of buns sold for that week ending date.

Here is the formula I created to get the correct weekly date for the week ending:
if {tblBudgetManufactured.EndDate} = {?WE Date}
and {tblCompany.CompanyName} = {?Bakery}
then {tblBudgetManufactured.Qty}*{tblBudgetManufactured.EaCount}/12

then I created this formula to get the total for the week:

sum ({@1st Wk Sum},{tblSKU.ShortDesc})

I also created these date formulas to use for the prior weeks:

{?WE Date} - 7

and then used this with the above 2 formulas to get the totals for the prior weeks dozens of buns sold.

Well again thank you. I shall try your approach, since I can see that I have been going about it all wrong and will write back soon. Have a great day and I really appreciate your help.

Sincerely,

Myla