Average of a Summarized Field

[CPK6104]

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.

 

[SkipVought]

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,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[CPK6104]

Thanks Skip. I have attached a snapshot of page 1. I hope this helps. Please let me know if you need additional info.

 

[CPK6104]

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

 

[SkipVought]

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,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[CPK6104]

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

 

[SkipVought]

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,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[CPK6104]

The table is calculating the average of the elapsed days field.

 

[SkipVought]

See my edited answer above.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[CPK6104]

Thanks for the help Skip. Much appreciated.