average of a count

[rmcguire80]

Hello,

I have a number of shipments and trying to get a count of the number of shipments, so

do a count of the SHIPMENTS.RECNO then get an average of the number of shipments based a specific number of days in a date range in this case the entry date range

thanks

 

[IanWaterman]

In report footer create formula

@AvgCount
Count( SHIPMENTS.RECNO )/datediff("d", StartdateParametre, Enddateparameter)

depending on your data structure you might have to use

distinctCount( SHIPMENTS.RECNO )/datediff("d", StartdateParametre, Enddateparameter)

Ian

 

[rmcguire80]

what do we put for the StartDateParameter? Im guessing I'll need to a a parameter field for a date range

 

[rmcguire80]

if I have my report group by individual user initials (SHIPMENTS.ENTEREDBY) how do I get the average per user?

thanks

 

[rmcguire80]

how would I exclude weekends?

 

[IanWaterman]

To count by user

@AvgCount
Count( SHIPMENTS.RECNO, SHIPMENTS.ENTEREDBY )/datediff("d", StartdateParametre, Enddateparameter)

To exclude weekends use Ken Hamady solution, you can also remove public holidays too.

http://kenhamady.com/form01.shtml

Ian

 

[IanWaterman]

Missed comment about date range, I assumed you were using date parameters to fix date range of report.

However, as you are grouping by user they may not have entries for every day in reporting range

To only count days for specific user data range change formula

@AvgCount
Count( SHIPMENTS.RECNO, SHIPMENTS.ENTEREDBY )/datediff("d", minimum(entrydate, SHIPMENTS.ENTEREDBY ) maximum(entrydate, SHIPMENTS.ENTEREDBY ))

This will count days between two limits, you will need to replace with result of Ken Hamady formula if you want to exclude weekends. Also days without shipments will still be counted.

Ian