INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Average of a Summarized Field

Average of a Summarized Field

(OP)
I have searched the threads for an answer because I know this question has been asked before. I was not able to find what I needed.

I'm performing some datediff math in a group header and this works perfectly:

ELAPSED DAYS:
if isnull({Sheet1_.Next Assessment Date}) then 30 else
(DateDiff ("d",Minimum ({@Next Assessment Date}, {Sheet1_.Legal Name}) , Maximum ({Sheet1_.Date Reported}, {Sheet1_.Legal Name})))

The next step is to average Elapsed Days in the Report Header. I added @Elapsed Days to a table to get an average. The results are close, but not correct.

Any ideas? Thank you.

RE: Average of a Summarized Field

Hi,

A picture is worth 1K words (kinda lost its ring)

Please post an cogent example of the data you're attempting to average, your current results and the expected results.

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: Average of a Summarized Field

(OP)
The black bar is group header 1.
The grey bar is group header 2.
The tables are in the Report Header.

RE: Average of a Summarized Field

We need numbers to work with as a basis for calculation. What are the NUMBERS that comprise the values that you are attempting to calculate? If the elapsed days is 55 then I want to see DATA where 55 is derived from and the corresponding assessor values from which you expect to get 60.1, not a pretty picture, please. DATA!

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: Average of a Summarized Field

(OP)
Thanks Skip. I re-ran the elapsed day figures below and the average should actually be 58.4. The average calculated in the table for assessor A is 63.9. It's close, but not close enough.

55 is the number of assessments for Assessor A.
The first 2 figures of the 55 Elapsed Day figures are 199 and 149 (red arrows in the pic).
Here's the full list of Elapsed Days:

199
146
39
120
24
58
30
30
117
275
64
36
30
28
30
32
80
9
101
60
30
15
22
52
66
31
36
36
137
59
19
61
71
3
64
22
83
64
41
66
127
59
27
30
30
18
129
26
30
22
106
30
30
36
26
avg 58.4

RE: Average of a Summarized Field

Quote:

The average calculated in the table for assessor A is 63.9.

Exactly how is this average calculated?

Chances are you have several one to many relationships in your query resulting in an inflation of the values in the numerator, which is distorting your average. You must get the set containing the values that you posted before calculating the average.

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: Average of a Summarized Field

(OP)
The table is calculating the average of the elapsed days field.

RE: Average of a Summarized Field

See my edited answer above.

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: Average of a Summarized Field

(OP)
Thanks for the help Skip. Much appreciated.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close