topic Re: running average in load script in QlikView

running average in load script

ronaldwang — Wed, 25 Nov 2020 16:16:04 GMT

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

Re: running average in load script

jonathandienst — Fri, 16 Feb 2018 05:09:28 GMT

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

Re: running average in load script

jonathandienst — Fri, 16 Feb 2018 05:16:20 GMT

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

Re: running average in load script

qlik4asif — Fri, 16 Feb 2018 05:42:34 GMT

You can create column with

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

or simple

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

Re: running average in load script

ronaldwang — Fri, 16 Feb 2018 05:43:19 GMT

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

Re: running average in load script

HONG_WEI — Wed, 29 Mar 2023 09:40:19 GMT

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