How to find average when we have duplicate records.

[Former Member]

Hello Guys,

I have to find the avg of Time

I have duplicate records in my report.

I have calculated the time difference in Seconds.

Ex:

ID Time

1    200s

2    300s

2    200s

3    100s

I have done sum{Time}/Distinctcount{ID}

Avg is calculated wrong now because of duplicate IDs.

I want to consider only the max time of duplicate record ie "300s" of ID "2"

Please help!

[former_member205840 · Accepted solution]

Hi Rizwan,

First create a group on ID

Drag all fields to group footer except Timie, Now insert a summary on time with Maximum() ( you may need to convert this to string)

Now write a running total to sum the maximum() values like below :

Whileprintingrecords;

Numbervar t_time;

T_time:= t_TIME+Tonumber(maximum(totext(@time,ID)))

Place above in group footer

Now create one more formula to arrive average :

Whileprintingrecords;

Numbervar t_time/ Distinctcount{ID}

Place this formula in your report footer to display Average.

Thanks,

Sastry

[Former Member · Accepted solution]

Thank you