AVG on X items

[#1]

Hi everybody.

I need you!

I would like to calculate the average of X first time values and i don't find the solution.

Exemple :

  Town             Date            Time

ARLON	1/01/2013	07:00
ARLON	1/01/2013	10:00
ARLON	1/01/2013	09:00
ARLON	1/01/2013	08:00
ARLON	2/01/2013	10:00
ARLON	2/01/2013	11:00
ARLON	2/01/2013	12:00
ARLON	2/01/2013	13:00
ARLON	2/01/2013	14:00
GENT	1/01/2013	06:00
GENT	1/01/2013	07:00
GENT	1/01/2013	08:00
GENT	1/01/2013	05:00

I would like to have the average of the 3 first times(chronological) for each town and each date.

So :

ARLON     1/01/2013     08:00   because the range is (07:00, 08:00, 09:00)

ARLON     2/01/2013     11:00   because the range is (10:00, 11:00, 12:00)

GENT       1/01/2013     06:00   because the range is (05:00, 06:00, 07:00)

I hope and will appreciate your help...

Thanks

[maxgro]

Hi

just an idea on which to experiment, see attachment

Regards

[#3 · Accepted solution]

Try like below:

Temp:

LOAD * , City&'-'&Date AS Key INLINE [

City , Date , Time

ARLON, 1/01/2013, 07:00

ARLON, 1/01/2013, 10:00

ARLON, 1/01/2013, 09:00

ARLON, 1/01/2013, 08:00

ARLON, 2/01/2013, 10:00

ARLON, 2/01/2013, 11:00

ARLON, 2/01/2013, 12:00

ARLON, 2/01/2013, 13:00

ARLON, 2/01/2013, 14:00

GENT, 1/01/2013, 06:00

GENT, 1/01/2013, 07:00

GENT, 1/01/2013, 08:00

GENT, 1/01/2013, 05:00

];

Temp_1:

LOAD * ,

IF(Key<>Previous(Key)  , 1 , 1+Peek(FLAG)) AS FLAG

Resident Temp Order By City , Date , Time;

DROP Table Temp ;

Final:

LOAD City , Date , Avg(Time) AS Time

Resident Temp_1

Where FLAG < 4

Group By City , Date ;

DROP Table Temp_1 ;

Please find the attached file for reference.

[#4]

Hi Dathu.qv

Hi Massimo Grossi

Thanks for your solution. That's fine.

Regards