Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
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
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
You can create column with
=Only(Aggr(RangeAvg(Above(Sum({1} Sales),0,10)), Product))
or simple
=RangeAvg(Above( Sum( Sales ), 0, 12 ))
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
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