How to show the average numbers?

[#1]

Hi All,

I have one table:

OrderId, StartDate, EndDate,

1,  2010-1-1, 2010-1-5

1,  2010-1-1, 2010-1-5

2,  2010-1-3, 2010-1-5

I want to show the average difference between StartDate and EndDate and show it in text object.

The result looks like this;

(4+2)/2=3

It should ignore the second record, how to use the expression to achieve it?

Thanks.

[swuehl]

Hi, if EndDate and StartDate are always same for identical OrderID (i.e. it doesn't matter which line of the two OrderIDs '1' is ignored), try this:

=avg(aggr(DISTINCT (EndDate-StartDate), OrderId))

Stefan