Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

ronaldwang
Contributor III

running average in load script

I have a table with three columns one is date , one is product and the other is sales. if i want to create a fourth column say previous 10 days' average sales for a product. what will be the most efficient way to do this i load script? thanks

1 Solution

Accepted Solutions
MVP
MVP

Re: running average in load script

Ahh, that does not work. I was trying for a single pass through the data, but perhaps tha's not possible. So version 2 is

Step1:

LOAD

Product,

    Date,

Sales,

    If (Previous(Product) = Product, Peek(RunCount) + 1, 1) as RunCount,

Order By Product, Date

Step2:

LOAD

Product,
Date,
Sales,

    RangeAvg(

      Sales,

      If(RunCount > 1, Previous(Sales)),

      If(RunCount > 2, Previous(Previous(Sales))),

      ...

      If(RunCount > 9, Previous(Previous....(Previous(Sales))))))))))

    ) as RunngAvg

Resident Step1

Order By Product, Date

DROP Table Step1;


You will need to flesh out the progressive Previous statements from 3 on

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein

View solution in original post

4 Replies
MVP
MVP

Re: running average in load script

A bit crude perhaps, but the best  I can think of right now:

LOAD

     ...

    Product,

    Date,

    If (Previous(Product) = Product, Peek(RunCount) + 1, 1) as RunCount,

    RangeAvg(

      Previous(Sales),

      If(Peek(RunCount) > 1, Previous(Previous(Sales))),

      If(Peek(RunCount) > 2, Previous(Previous(Previous(Sales)))),

      ...

      If(Peek(RunCount) > 9, Previous(Previous....(Previous(Sales)))))))))))

    ) as RunngAvg

    ...

FROM ...

Order By Product, Date

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
MVP
MVP

Re: running average in load script

Ahh, that does not work. I was trying for a single pass through the data, but perhaps tha's not possible. So version 2 is

Step1:

LOAD

Product,

    Date,

Sales,

    If (Previous(Product) = Product, Peek(RunCount) + 1, 1) as RunCount,

Order By Product, Date

Step2:

LOAD

Product,
Date,
Sales,

    RangeAvg(

      Sales,

      If(RunCount > 1, Previous(Sales)),

      If(RunCount > 2, Previous(Previous(Sales))),

      ...

      If(RunCount > 9, Previous(Previous....(Previous(Sales))))))))))

    ) as RunngAvg

Resident Step1

Order By Product, Date

DROP Table Step1;


You will need to flesh out the progressive Previous statements from 3 on

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein

View solution in original post

qlik4asif
Contributor II

Re: running average in load script

You can create column with

=Only(Aggr(RangeAvg(Above(Sum({1} Sales),0,10)), Product))

or simple

=RangeAvg(Above( Sum( Sales ), 0, 12 ))

ronaldwang
Contributor III

Re: running average in load script

Thanks, but if I want to said calculate 90 days average, then I have to use 90 previous function? I am ok to leave the product with 90 days history not calculating the running average