Davg in report group footer

[VictoriaHood]

I am trying to get an average as follows:

=Davg("dayspastdue","div30qry","dayspastdue >=0")

This works in giving me the overall average (of overdue tickets) for the entire report. I need to get the same average per group [salesperson] and it doesn't seem to adjust for the group. I'm sure I need to add another criteria, but don't know how. . .

Any help would be greatly appreciated!

[Ranman256]

in the report, add a GROUP band (for what ever group you need)
set it WITH FOOTER
in the footer , put your AVG( field).
(you don't need DAVG, but you can)

[theDBguy]

Hi Victoria. Are you saying a simple Avg() function in the group footer doesn't work?

[VictoriaHood]

I have the group set with header/footer. It is grouped on [salesperson]. I need to average only tickets that are overdue, i.e., those that show as 0 or greater days. I tried to use Avg(IIf([dayspastdue]>=0,1,0)) but it keeps returning "1" for every salesperson. [dayspastdue] is a calculated field in the underlying query (based on two dates).

[theDBguy]

Originally Posted by VictoriaHood

I have the group set with header/footer. It is grouped on [salesperson]. I need to average only tickets that are overdue, i.e., those that show as 0 or greater days. I tried to use Avg(IIf([dayspastdue]>=0,1,0)) but it keeps returning "1" for every salesperson. [dayspastdue] is a calculated field in the underlying query (based on two dates).

Hi Victoria. Are you able to post a copy of your db with enough test data to duplicate the problem?

[isladogs]

Just to clarify, if you used an expression like Date()-[DueDate] AS DaysPastDue in your query, you can't reliably use DaysPastDue in another field in the same query such as your IIf expression.
You must use the full expression again for the derived field e.g.

SELECT DueDate, Date()-[DueDate] AS DaysPastDue, Iif((Date()-[DueDate])>0,1,0) AS Overdue
FROM TableName;

Alternatively you can use IIf(DaysPastDue>0,1,0) in a second query based on the first

[Pat Hartman]

When calculating overdue, return null rather than 0 when the item is not overdue. The domain functions will ignore nulls but will include zero so:

Avg(3,0,3) = 2
BUT
Avg(3, null, 3) = 3

Then the control for the average would have a simple expression as its ControlSource:

=Avg(OverDueDays)