Calculate an average of last 4 data points in load script

[Anonymous]

Hi all, I'm loading weekly 'unit sales' data into a qlik app thru a load script that is across multiple products, across multiple sellers.  For each week, I want to calculate the average of the unit sales of the previous 4 weeks.  I'm currently loading this data into an ORACLE database first, and running script there to properly order the data (by Seller, then product, then week) and make this calculation, and want to make this change so I can load the data directly to qlik and skip the ORACLE step.  I was looking at the 'Peek' function and looks like it may be easy to get the value for the previous period, but how can I grab the most recent 4 periods and perform an average calculation on them?  I think the order by Seller, product, week step will help me get the data in the right order to leverage this, then I just need to ensure my solution handles the first few weeks of each year, and the first few weeks of data per seller and model, correctly...thinking an if..then could help me there.  Can someone help explain if there is a functon or technique I could leverage in the load script to accomplish this?  I'm really trying to avoid the calc in the app as my app is already pretty calculation heavy so I'm concerned about performance.  Thank you for any help!!  I do not have an example app since the data is sensitive, but really just need to be pointed in the right direction anyway so can take guidance and run with it.  (note, the data has fields for week_start_date, week 3, and year, so I have flexibility to leverage any of these values)

[MarcoWedel]

Hi,

maybe one solution to create this 4 week average field in the load statement could be:

LOAD ...,
          If(Seller=Previous(Previous(Previous(Seller))) and product=Previous(Previous(Previous(product))), RangeAvg(UnitSales, Previous(UnitSales), Previous(Previous(UnitSales)), Previous(Previous(Previous(UnitSales))))) as UnitSales4WeekAvg
From Your Source
Order By Seller, product, week;

Instead of the nested Previous() functions you might as well use Peek(UnitSales), Peek(UnitSales,-2), Peek(UnitSales,-3) ...

Despite your performance doubts you should try to implement a front end solution first as it should deliver a more dynamic experience than this precalculated aggregation.

hope this helps

regards

Marco