Formula Problem using CalendarQtrs 1

[Myla944]

Good Afternoon,

I am using Crystal Version 9 and SQL Server 2000 (Sales Database).

I have created a Dz Mfg report and I am having a problem with the calender1stqtr (actually all quarters). The amount returned is not correct, so I am using this formula incorrectly or doing the wrong formula to get this information. I am also having a problem with the YTD formula.

Here is how my report looks:

W/E 5/15/04 W/E 5/8/04 QTD YTD

Regs 8025 7450 20 1275
Qtrs 758 620 128 270

Here is my formula for the QTD formula:

if {tblBudgetManufactured.EndDate} = Calendar1stQtr and {tblCompany.CompanyName} = {?Bakery} then {tblBudgetManufactured.Qty}*{tblBudgetManufactured.EaCount}/12
else if {tblBudgetManufactured.EndDate} = Calendar2ndQtr and {tblCompany.CompanyName} = {?Bakery} then {tblBudgetManufactured.Qty}*{tblBudgetManufactured.EaCount}/12
else if {tblBudgetManufactured.EndDate} = Calendar3rdQtr and {tblCompany.CompanyName} = {?Bakery} then {tblBudgetManufactured.Qty}*{tblBudgetManufactured.EaCount}/12
else if {tblBudgetManufactured.EndDate} = Calendar4thQtr and {tblCompany.CompanyName} = {?Bakery} then {tblBudgetManufactured.Qty}*{tblBudgetManufactured.EaCount}/12


Here is my YTD formula:

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

I created this formula for the week ending date using a parameter that allows the user to select the WE Date for the first WE Date on my report and it is working great:

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

I created this formula to select the 2nd WE Date and it is working great:

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



I am at a loss has to what I am doing wrong. I went through the tech questions/information here on the site and could not find anything related to what I am doing.
Please help!

Thank you and have an awesome day today!

Sincerely,
Myla

 

[dgillz]

In your first formula, all 4 then statements are identical. Is this what you want?

Software Sales, Training, Implementation and Support for Exact Macola, eSynergy, and Crystal Reports

http://www.trianglepartners.com

 

[Myla944]

Hello dgillz,

No I want it the formula to show how many manufactured buns were created each quarter based on the parameter Week Ending Date (WE Date) that the user selects. Are week ending date (which is a Saturday date) is used in our Manufactured Dozens Database in our Sales System (tble.BudgetManufactured.EndDate).

When the user selects 5/18/04 which is our Week Ending Date, I only want the total manufactured dozens shown for 2nd Qtr . If the user selects 3/13/04, then the formula should only show what was manufactured for 1st Qtr. I hope this all makes sense and thank you for replying to my post.

Sincerely,

Myla

 

[dgillz]

My point is that your formula, no matter what the date is, provided it is in this year and provided the company name is equal to {?Bakery} will ALWAYS perform the same calculation, namely:

{tblBudgetManufactured.Qty}*{tblBudgetManufactured.EaCount}/12

This will be true for EVERY Record. Is this what you want?


Software Sales, Training, Implementation and Support for Exact Macola, eSynergy, and Crystal Reports

http://www.trianglepartners.com

 

[Myla944]

Hello dgillz,

I see what you are talking about. I am sorry, when I created the Week Ending formula, I used 2 formulas. One I created but did not place on the sheet:

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

then I created this formula to use in the report to sum the 1st formula and it works:

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

I tried this with the quartly and year to date formula and it does not work when I use the (?WE Date) parameter. I tried using the {tbl.BudgetManufacture.EndDate} and it works but it will not match the date the user selects, but will instead always show the current QTD and YTD.

Here is the formula's that work, using 2 as I did for the Week Ending Date formulas above:
1st QTD Formula
if {tblBudgetManufactured.EndDate} in Calendar2ndQtr
and {tblCompany.CompanyName} = {?Bakery}
then {tblBudgetManufactured.Qty}*{tblBudgetManufactured.EaCount}/12
2nd QTD Formula

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

I do not understand why I cannot use the parameter date in the first formula above, as it appears not to show the incorrect data and I do not understand how to have the formula select all the quarters.

Here is what I tried on the YTD Formula:
1st Formula
if {tblBudgetManufactured.EndDate} in yeartodate
and {tblCompany.CompanyName} = {?Bakery}
then {tblBudgetManufactured.Qty}*{tblBudgetManufactured.EaCount}/12
2nd Formula
sum ({@YTD},{tblSKU.ShortDesc})

Same problem with the 1st formula not showing the correct YTD when I use the parameter date. I have used the parameter date on the WE formula and it worked fine, so I am stumped.

 

[dgillz]

There is no reason you cannot use a parameter date in this formula.

I suspect you are not telling us all the details necessary to write this report. Why for instance would the 2nd qtr formula be a sum() when the first one is not?

You also make reference to a "WE" formula, but you never posted it, or maybe you posted it but did not identify it as the "WE" formula.

Also, please advise where these formulas are placed? Are they all in the group footer, details section, or what?

Software Sales, Training, Implementation and Support for Exact Macola, eSynergy, and Crystal Reports

http://www.trianglepartners.com

 

[Myla944]

Good Morning,

I have tried to use the parameter in the Qtr & YTD and for some reason it gives me the wrong information, so I do not understand what I am doing wrong. Also thanks for putting that site link on your post, I went there and wrote the person in Texas for on-site training as my supervisor said that after I figured what I could on my own that I could have someone come down and show me more.

Well thank you for trying to help. I am not that great at explaining things and I will just have to somehow figure this one out.
I hope you have an awesome day today and again I appreciate you taking the time to assist me.

Sincerely,
Myla