AVG of the first dates of the selection

[#1]

Hi,

Im making an applications with different tables with many data. Lets look at his example what causes the trouble (This is just an example, with dummy data to make the problem clear):

ID	DD_AANVR	DD_AFDOENING
1	1-1-2001	4-1-2001
1	4-1-2001	8-1-2001
1	8-1-2001	18-1-2001
1	18-1-2001	23-1-2001
2	1-1-2001	3-1-2001
2	3-1-2001	8-1-2001
2	8-1-2001	18-1-2001
2	18-1-2001	21-1-2001
3	1-2-2001	4-2-2001
3	4-2-2001	8-2-2001
3	8-2-2001	18-2-2001

I want to take the AVG of the amount of days between the begin and end dates of the ID's, but only from the first begin and end date.  So far i have this expression:

AVG(if(DD_AFDOENING >= DD_AANVR, DD_AFDOENING - DD_AANVR))'

Does somebody know how i can solve this?

[Gysbert_Wassenaar · Accepted solution]

Not quite sure what you want.

Average of days between the minimum DD_AANVR and maximum DD_AFDOENING per ID:

avg(aggr(max(total <ID> DD_AFDOENING) - min(total <ID> DD_AANVR),ID))

Average of days between the first DD_AANVR and first DD_AFDOENING per ID:

avg(aggr(FirstSortedValue(DD_AFDOENING - DD_AANVR, DD_AANVR),ID))

see attached qvw

[#3]

The 2nd one did the trick thanks.