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.
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:
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;
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.