running average in load script

[ronaldwang]

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

[jonathandienst]

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

[jonathandienst · Accepted solution]

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

[qlik4asif]

You can create column with

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

or simple

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

[ronaldwang]

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

[HONG_WEI]

 Good try but Above is a chart function, can't use in Load script editors. Maybe you need think of other ways

if you wanna solve it in load editor level