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

# 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:

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

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

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

group by ClientID;

Next i

• ###### Re: Help with looping through dates

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