# QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Rules, plus terms and conditions, can be found here.
Not applicable

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

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

Tags (4)
1 Solution

Accepted Solutions
MVP

## Re: Help with looping through dates

Maybe like this:

for i =  1 to FieldValueCount( 'Date' )

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

Result:

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

2 Replies
MVP

## Re: Help with looping through dates

Maybe like this:

for i =  1 to FieldValueCount( 'Date' )

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

Result:

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

Not applicable

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