Average Monthtodate,YTD and L4Weeks

[jehanzebn]

Dear all,

I have created a report which is grouped by Reorder code, the report counts number of jobs(Running Total),number of lenses(Running Total) and average per day of jobs and lenses (Formula).

Formula for average per day

Whileprintingrecords;
Numbervar JAvg;
Numbervar myaverage=0;
Numbervar CAvg=0;
myaverage := {#Total Jobs};  
CAvg:= myaverage /({@EndDate}-{@StartDate}+1);
JAvg:=JAvg + CAvg;
CAvg

What I would like to add in the same report is Month to date, Year to Date and Last 4 weeks averages. I tried the following code, which works fine if I create a separate report with single Month to date formula but this doesn't work within the current report. Meaning for some reason I cannot use the MTD formula under same report. Similarly with YTD and L4W.
Code for MTD Average

Whileprintingrecords;
Numbervar FAvg;
Numbervar myaverage=0;
Numbervar CAvg=0;
myaverage := {#Total Lenses};  
CAvg:= myaverage /Day(Maximum(MonthToDate));
FAvg:=FAvg + CAvg;
CAvg

Please note: The date is already set up to current date i.e. I am using two current date formulas.

Report Record Selection formula

{lab_rework.rework_date} in {@StartDate} to {@EndDate} and
{lab_reasons.reason_code} >= {?startcode} and
{lab_reasons.reason_code} <= {?endcode}

Any help would be grateful appreciated.

many thanks

Regards

Jehanzeb

 

[mocgp]

There's not quite enough information here to be very specific. It would be helpful to know which version of Crystal and what type of database is in use. We also need to know how your 2 Running Totals are structured. If I assume that you have a valid {lab_rework.rework_date} for each row that you are accumulating in the Running Totals, then here is something that might work:

First of all, the only way you can show multiple date summaries in the same report is to run the report for the broadest date summary desired, in this case, the Year-To-Date. So your Start Date is going to be 1/1/2008 and your end date is going to be the current date. Once you have all of the ytd records in the report....

Average your Job and Lenses counts for YTD in a regular Crystal summary (I am assuming you are creating one summary by Reorder Code and one Grand Total. This will be the correct average ONLY if you are running it for the date range indicated above.

Create Running Totals for Job and Lenses for your age targets L4W and MTD. Summarize (average) on your job / lens fields, then Evaluate on a formula that says

{lab_rework.rework_date} in MonthToDate


for example. Use Last4WeeksToSun in the other RT(s). Note that Cyrstal's L4W is fixed at Last 4 Weeks to Sunday (hope that is agreeable, otherwise you will have to create a local formula to range Last 4 Weeks to today - or whatever your end-date target was).

If my assumptions are correct, this will probably get you what you need.

 

[jehanzebn]

mocgp I agree with you. I am sorry for not informing about Crystal version (Actually I usually tell this very same question to others and this time I forgot myself).

Right back to work:

Crystal Version: 2008 Dev
Database: Informix
Structure of Running Totals:

Jobs
-----
Count of rework number on each record and reset upon change in Group (reason code).

Lenses
------
Sum of rework quatitiy on each reocrd and reset on change of group (reason code).

I have running formulas provided in my initial post placed under group footer and grand totals under Report footer.

I agree with you again on the broader date range however what end user wants is daily report however at the same time want to see the average of YTD, MTD and L4Ws.

So I cannot change the date range from current to yeartodate.

Do you think Crosstab might be able to help here?

Regards

Jehanzeb