topic AVG on X items in QlikView

AVG on X items

Mon, 13 Jan 2014 22:19:54 GMT

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

maxgro — Mon, 13 Jan 2014 23:19:51 GMT

just an idea on which to experiment, see attachment

Regards

Re: AVG on X items

Mon, 13 Jan 2014 23:40:22 GMT

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.

Re: AVG on X items

Tue, 14 Jan 2014 09:53:15 GMT

Hi Dathu.qv

Hi Massimo Grossi

Thanks for your solution. That's fine.

Regards