3 Replies Latest reply: Jan 14, 2014 4:53 AM by PIET DETERM

# AVG on X items

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

• ###### Re: AVG on X items

Hi

just an idea on which to experiment, see attachment

Regards

• ###### Re: AVG on X items

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:

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.

• ###### Re: AVG on X items

Hi D