Different Averages being calculated

[shoaibmulla]

Hi

I have used the duration field in a simple table where duration is being displayed against each record and Totals (Average is being shown).

=IF(
(date([Timeline_Forecasts.Forecast CP1 Date])> date([Timeline_Forecasts.Forecast Project Start])),
date([Timeline_Forecasts.Forecast CP1 Date])- date([Timeline_Forecasts.Forecast Project Start])
)

However, If I use a similar formula in a table that groups certain categories the result is completely different. The formula I have used is

=Avg(
IF(
date([Timeline_Forecasts.Forecast CP1 Date])> date([Timeline_Forecasts.Forecast Project Start]),
date([Timeline_Forecasts.Forecast CP1 Date])- date([Timeline_Forecasts.Forecast Project Start])
)
)

The one displayed in the details table is correct average. Would appreciate your help.

[sunny_talwar]

Can you try this same expression for both the tables and see if this works

=Avg(RangeMax([Timeline_Forecasts.Forecast CP1 Date] - [Timeline_Forecasts.Forecast Project Start], 0))

[shoaibmulla]

Hi Sunny

Thank you very much for the response but it didn't work in either of the tables (detailed and summary). In detail table it replaces null values with 0, which makes the average go off. The formula I had used earlier was providing the correct results for detailed table.

Shoaib

[shoaibmulla]

I was able to get the correct average by using the following formula for the summary table.

=Avg(
	Aggr(
          (IF(
             (date([Timeline_Forecasts.Forecast CP1 Date])> date([Timeline_Forecasts.Forecast Project Start])),
              date([Timeline_Forecasts.Forecast CP1 Date])- date([Timeline_Forecasts.Forecast Project Start]))),[Server.ProjectName]
             )
     )