Script Average all values that have bigger dates

[cdy1]

Hi, 

I have a set of data that includes dates and amounts. For each date on the script i need to calculate a fielad that has an average of all amount that have dates bigger or equal to the date of the row. Every day a value will be added so the last date will be moving and all the averages will be recalculated. 

I leave an example in the uploaded excel. 

Thanks! 

[Kushal_Chawda]

@cdy1  if you have unique records per each day then try below. If you don't have unique records per date then you first need to create aggregated table which holds unique records per each date, then you can try below method

Data:
LOAD date, 
     amount
FROM
[qlik avg below.xlsx]
(ooxml, embedded labels, table is Sheet1);

Final:
LOAD *,
     Accum/RowNo() as Avg_Amt;
LOAD *,
     rangesum(amount,peek('Accum')) as Accum
Resident Data
Order by date desc;

DROP Table Data;

DROP field Accum;