Davg in report group footer (1 Viewer)

VictoriaHood

New member
Local time
Today, 06:01
Joined
Jan 31, 2013
Messages
4
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

Well-known member
Local time
Today, 06:01
Joined
Apr 9, 2015
Messages
4,337
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

I’m here to help
Staff member
Local time
Today, 03:01
Joined
Oct 29, 2018
Messages
21,357
Hi Victoria. Are you saying a simple Avg() function in the group footer doesn't work?
 

VictoriaHood

New member
Local time
Today, 06:01
Joined
Jan 31, 2013
Messages
4
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

I’m here to help
Staff member
Local time
Today, 03:01
Joined
Oct 29, 2018
Messages
21,357
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

MVP / VIP
Local time
Today, 10:01
Joined
Jan 14, 2017
Messages
18,186
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.
Code:
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

Super Moderator
Staff member
Local time
Today, 06:01
Joined
Feb 19, 2002
Messages
42,970
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)
 

Users who are viewing this thread

Top Bottom