# New to QlikView

Discussion board where members can get started with QlikView.

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

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

 Product,

Date,

 Sales,

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

 Order By Product, Date

Step2:

 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
4 Replies
MVP

## Re: running average in load script

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

...

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

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

 Product,

Date,

 Sales,

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

 Order By Product, Date

Step2:

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

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