Average duration of something formatted in HH:MM:SS

[elsenorjose]

This kind of question has been asked time and again but I can't seem to modify the usual formulas to fit my needs.

I have a field which captures the duration of a media file in seconds. In my report, I have groupings by filename and would like to calculate the average time a file is played and display it in HH:MM:SS (usually it will just be MM:SS). Some sample data:

Filname Duration (in seconds)
1 100
1 200
1 300
1 400
2 500
2 600
2 700
2 800
3 900
3 1000
3 1100
3 1200

Desired Output:

Filname Avg. Duration in Mins.
1 4:10
2 10:10
3 17:30

Avg for all: 10:30

I can insert a summary to give me the average across the groups and the grand total but this displays in seconds and as decimals. If I use a ToText formula to format the time in HH:MM:SS format, I can't do any math on it. What am I missing?

Thanks!

 

[synapsevampire]

Check out my FAQ:

faq767-3543

So you would use a:

SUM({table.seconds},{Table.Filename})

as the feed for the seconds in my formula and place the formula in the group footer.

-k

 

[elsenorjose]

K,

Thanks. I'm almost there. This works perfectly for the groups but when I use the formula in my report footer (that's where I have my grand totals) I get the last average in my group, i.e.; Filename 3's average, not the average across all groups. Do I need to create another grouping level and average across that?

 

[synapsevampire]

You'll need another variable to squirrel away the group totals.

So for the formula in the group footer use:

whileprintingrecords;
numbervar TotalSecs:=TotalSecs+SUM({table.seconds},{Table.Filename});
SUM({table.seconds},{Table.Filename})

Then in the report footer use:

whileprintingrecords;
numbervar TotalSecs;
and then use TotalSecs as the sum of the seconds for the formula for displaying the HH:MM:SS.

-k

 

[elsenorjose]

Slight mod and it worked:

whileprintingrecords;
numberVar dur := sum({table.seconds})/count({Table.Filename});
numberVar min;
numberVar sec;
stringVar mmss;

min := Remainder(Truncate(dur/60),60);
sec := Remainder(dur,60);

mmss := totext(min,"0") +":" + totext(sec,"00");

mmss

Put it in the footer and it gave me the correct calc.

As always, thanks for the help!