Help with looping through dates

[#1]

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

[swuehl · Accepted solution]

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

[#3]

Thanks so much, worked like a charm on my expanded data set.

I don't think I'd have been able to progress this, although having a loop example on my own set of data has given me a greater understanding of the concept.

Again thanks