Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
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
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