topic Script Average all values that have bigger dates in Search the Community

Script Average all values that have bigger dates

cdy1 — Sat, 31 Jul 2021 23:06:00 GMT

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!

Re: Script Average all values that have bigger dates

Kushal_Chawda — Sun, 01 Aug 2021 13:39:11 GMT

@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;