Average of formula - CR9 1

[CalgaryCR9]

My formula: (crystal reports 9)
NumberVar TotalSec := datediff("s",NthLargest (1, {@Open Time}, {SERVICE_CALL.SERVICE_CALL_ID}),NthLargest (1, {@resolved}, {SERVICE_CALL.SERVICE_CALL_ID}));

generates a number. Sample data:
TIME RESOLVED (not in date format, but in integer format)
group 1 header SERVICE_CALL.ASSIGNED_TECH = Joy
group 2 header SERVICE_CALL.SERVICE_CALL_ID = 123
7843453
group 2 footer SERVICE_CALL.SERVICE_CALL_ID= 123
group 2 header SERVICE_CALL.SERVICE_CALL_ID =124
0
group 2 footer SERVICE_CALL.SERVICE_CALL_ID=124
group 2 header SERVICE_CALL.SERVICE_CALL_ID = 125
0
group 2 footer SERVICE_CALL.SERVICE_CALL_ID = 125
group 2 header SERVICE_CALL.SERVICE_CALL_ID =126
345234
group 2 footer SERVICE_CALL.SERVICE_CALL_ID =126
group 1 footer SERVICE_CALL.ASSIGNED_TECH = Joy

group 1 header SERVICE_CALL.ASSIGNED_TECH = Jim
group 2 header SERVICE_CALL.SERVICE_CALL_ID = 456
789454
group 2 footer SERVICE_CALL.SERVICE_CALL_ID= 456
group 2 header SERVICE_CALL.SERVICE_CALL_ID = 789
0
group 2 footer SERVICE_CALL.SERVICE_CALL_ID= 789
group 1 footer SERVICE_CALL.ASSIGNED_TECH = Jim

I want to average the time resolved (7843453,345234,789454) EXCLUDING the calls where time resolved = 0.

How do I do this?

Joy

 

[lbass]

Create a formula and place it in GH#2:

whileprintingrecords;
numbervar cnt;
numbervar sumdiff := sumdiff + {@diff};
if {@diff} <> 0 then
cnt := cnt + 1;

Then create a display formula for the report footer:
whileprintingrecords;
numbervar cnt;
numbervar sumdiff;
sumdiff/cnt

-LB

 

[CalgaryCR9]

Thanks. It's going to be a little while before I can try it out, but I totally appreciate your speedy reply.

I'll post my success/failure.

Joy

 

[CalgaryCR9]

My original formula was called: @convert_resolved_number.

Your suggestion gave me an error (division by zero)

header forumla:
whileprintingrecords;
numbervar cnt;
numbervar sumdiff := sumdiff + {@convert_resolved_number};
if {@convert_resolved_number} <> 0
then
cnt := cnt + 1;

footer formula:
whileprintingrecords;
numbervar cnt;
numbervar sumdiff;
sumdiff/cnt

I think the problem is that some of my technicians (ie: group 1) will not have the output from {@convert_resolved_number}.

Suggestions?

 

[lbass]

You mean the formula will return 0, right?

Change the display formula to:

whileprintingrecords;
numbervar cnt;
numbervar sumdiff;
if cnt > 0 then
sumdiff/cnt

-LB

 

[CalgaryCR9]

PERFECT!! Now how do I get an average for Group 1 in my Grand Total footer.

Joy = 38944212
Jason = 23497
Morgan = 456441
Vipin = 0

Grand total average = x

 

[CalgaryCR9]

{@average_display_footer} is
whileprintingrecords;
numbervar cnt;
numbervar sumdiff;
if cnt > 0 then
sumdiff/cnt

This seems to work:

whileprintingrecords;
numbervar cnt;
numbervar sumdiffer := sumdiffer + {@average_display_footer};
if {@average_display_footer} <> 0
then
cnt := cnt + 1;

whileprintingrecords;
numbervar cnt;
numbervar sumdiffer;
if cnt > 0 then
sumdiffer/cnt