Help averaging a date total.

[crelle]

I have searched other postings but haven’t found one that helps my issue, so I decided to post.

Info:
Crystal Reports XI
Group 1 sorted by Assigned Date
Group 2 sorted by Client Reason Code

This formula gives me a total number of days between assigned date and payment date.

//DateDiff ("d", {DBR.DBR_LAST_CHG_DATE_O},{DBR.DBR_ASSIGN_DATE_O})

I have this formula in the report in the group footer 2 because report is grouped by monthly totals only. My detail section is suppressed with just detail totals in group footer 2 and monthly totals in group footer 1 and report totals in the report footer.

My issue:
I use this formula under a heading called “Total Lag”. In the Group Footer I would like to have an average of these totals. But every time I try to put in an average it gives me an average of all the details that the formula is pulling from.
Right now I just end up exporting it to Excel and putting in my own average formula from there. How can I get it to average just the totals showing?

Simplified Example:

Month/Year
Reason Code | Total Assigned | Amount | Total Lag
C – Collected 5 $200.00 11
R – Renewed 8 $250.00 23

Monthly Totals: 13 $450.00 17 <--Average

 

[lbass]

It would be helpful to see data at the detail level, because your datediff formula will only be based on the last detail record in the group. Is that what you intend? Or is {@TotalLag} actually a summary of the datediff formula? Anyway, you can do the average at the monthly group level by creating three formulas:

//{@reset} to be placed in the date(monthly) group header:
whileprintingrecords;
numbervar moave;
if not inrepeatedgroupheader then (
moave := 0;
cnt := 0
);

//{@accum} to be placed in GF #2:
whileprintingrecords;
numbervar moave := moave + {@TotalLag};
numbervar cnt := cnt + 1;

//{@display} to be placed in GF#1:
whileprintingrecords;
numbervar moave;
numbervar cnt;
moave/cnt

-LB

 

[crelle]

Sorry it is hard for me to explain how i have it set up, i will try:

Group Header #1 - Assigned Date(Date)
Group Header #2 - Reason Code(String)
Details - Suppressed
Footer #2 - Reason Code(string) / Sums of details / @totallag
**the @totallag is
//DateDiff ("d", {DBR.DBR_LAST_CHG_DATE_O}, DBR.DBR_ASSIGN_DATE_O})
It is not summed, but shows total for each reason code in that month. This is displaying correctly.
Footer #1 - Subtotals of Footer #2, monthly. This is the area where i need the average of the @totallag to show.


I entered your formulas but @reset is giving me an error message, so i have not been able to verify if this works.

Thank you so much for assisting me, this has been driving me nuts trying to figure it out.

 

[lbass]

Please post the reset formula exactly as you set it up and also post the error message.

-LB

 

[crelle]

I just copied what you had typed:
(attached a screenshot)

 

[lbass]

Oops, that should have been:

//{@reset} to be placed in the date(monthly) group header:
whileprintingrecords;
numbervar moave;
numbervar cnt; [red]//forgot to declare this variable[/red]
if not inrepeatedgroupheader then (
moave := 0;
cnt := 0
);

-LB

 

[crelle]

oh thank you very much, it seems to be working now!!!