Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
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:

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

1 Solution

Accepted Solutions
MVP
MVP

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

Resident testload

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

          group by ClientID;

Next i

2 Replies
MVP
MVP

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

Resident testload

          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

Community Browser