Finding the Moving Average of a Data Series

[khenapart]

Hi Again!

I am looking to calculate the moving average of a data series (e.g. MA 7 days). Is it possible to do this by going to the Data Manager tab > Calculated Field > Creating an Expression for the moving average?

Or do I need to write a script? I assume that's under Data Manager > Data Load Editor.

Thanks in advance for any advice!

[chematos]

I did once a moving average in the script. Not sure if it is the right or more efficient way but the concept was to create a cumulative add of the previous value, so you need to be careful ordering your dataset ASC by Date. Let's say you have Date, Dim1, Metric1:

ATemp:

Load DATE,

Dim1,

Metric1

FROM Table;

You need to iterate by a Dim1 value list:

for Each vDim1 in $(vDim1List)

A:
LOAD *,
rangesum(Metric1,peek('Dim1_1')) as Dim1_1
Resident ATemp
order by DATE Asc;

Then, you create a new column by looking for the X moving avg value you need

B:
Load *,
RowNo()-7 as Rowno7
resident A;

DROP Table A;

Next vDim1

Finally, you calculate the moving avg, having in mind that the first X rows will not have an average:

Rolling:
LOAD *,
if(num(DATE)=num('1/8/2023'),Dim1_1/7,if(num(DATE)>num('1/8/2020'),(Dim1_1-peek('Dim1_1',Rowno7-1))/7,Dim1)) as RollingAvg
from table (qvd);

You will need to adapt this code for sure but hopefully it helps. 

Regards