Help with looping through dates

Fri, 15 Feb 2013 18:18:34 GMT

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

swuehl — Sat, 16 Feb 2013 00:38:58 GMT

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

Re: Help with looping through dates

Mon, 18 Feb 2013 13:48:58 GMT

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

topic Help with looping through dates in QlikView

Help with looping through dates

Re: Help with looping through dates

Re: Help with looping through dates