2 Replies Latest reply: Feb 18, 2013 8:48 AM by bagshot01 RSS

    Help with looping through dates

      Hi,

       

      I'm trying to calculate a 4 week average for each Date and ClientID that appears in a table.

       

      Based on the Date (which increases in full weeks) and ClientID I need to look back over the past 4 weeks (inclusive of current date) and get a calcualted average Quality socre.

       

      I have the following example table:

       

      testload:

      LOAD * INLINE [

           Date,                                         ClientID,    Quality

          06/12/2012,                1,                                                   1.8

          13/12/2012,                1,           1.5

          20/12/2012,                1,           2.3

          27/12/2012,                1,                                                   2.1

          03/01/2013,                1,                                                   1.1

          03/01/2013,                2,                                                   1.6

          10/01/2013,                1,                                                   2.6

          17/01/2013,                1,                                                   3.2

          24/01/2013,                1,                                                   1.4

          24/01/2013,                1,                                                   2.2

          31/01/2013,                1,                                                   2.1

          31/01/2013        2,                                                  3.4

          31/01/2013,                1,                                                   1.2

      ];

       

      For each distinct Date I need to be able to go back and calculate the 4 week average for each ClientID.

       

      Hopefully the below will assist with the explaination.

       

      Result:

      Date               ClientID          Quality     Count          Average     StartDate     

      31/01/2013      1                    12.7        6                 2.12          10/01/2013

      31/01/2013      2                    3.4          1                 3.4           10/01/2013

      24/01/2013      1                    9.5          5                 1.9            03/01/2013  

      24/01/2013      2                    1.6          1                 1.6            03/01/2013

      etc until it gets to:

      06/12/2012     1                    1.8          1                  1.8            15/11/2012  

       

      I think I'd need to use a for each loop but truth be told I don't even know where to start.

       

      Any assistance or pointers you can provide will be greatly appreciated.

       

      Thanks

        • Re: Help with looping through dates
          Stefan Wühl

          Maybe like this:

           

          for i =  1 to FieldValueCount( 'Date' )

           

          Let vDate = FieldValue('Date',$(i));

           

          Result:

          LOAD '$(vDate)' as DateEvaluated,

                     sum(Quality) as SumQuality,

                     count(Quality) as CountQuality,

                     avg(Quality) as AvgQuality,

                     ClientID,

                     Date('$(vDate)'-21) as StartDate

          Resident testload

                    where Date >= '$(vDate)'-21 and Date <= '$(vDate)'

                    group by ClientID;

           

          Next i