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

    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

      ARLON1/01/201307:00
      ARLON1/01/201310:00
      ARLON1/01/201309:00
      ARLON1/01/201308:00
      ARLON2/01/201310:00
      ARLON2/01/201311:00
      ARLON2/01/201312:00
      ARLON2/01/201313:00
      ARLON2/01/201314:00
      GENT1/01/201306:00
      GENT1/01/201307:00
      GENT1/01/201308:00
      GENT1/01/201305: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
          Massimo Grossi

          Hi

          just an idea on which to experiment, see attachment

          Regards

          • Re: AVG on X items
            Srikanth P

            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

              Hi D