-
-
-
Re: How to calculate moving avergae
Hi,
I've attached an excel to show you what I mean (No pan intended ).
The interval is 4 but can change.-
Re: How to calculate moving avergae
What do you mean by interval, I see initial 3 values are N/A, could you see the one I attached?
-
Re: How to calculate moving avergae
Pl see the updated one using latest data, I think rows 29-31 is some typo, as N/A doesn't make sense there.
-
Re: How to calculate moving avergae
Also I needed new ID field to generate the output as desired in the sample data.
-
Re: How to calculate moving avergae
Hi,
I think you are on the right track, the process & run_id creates a unique key. Each data set has a unique key and on that data we need calculate the moving average. The N/A is not a typo, when the function has not enough data
(in this case 4) it returns n/a.
I've tried using your function in my table and I get "invalid dimension". Any idea why?-
Re: How to calculate moving avergae
I added ID key field in script to make records unique. Let me know if you get error even after doing that.
-
-
-
-
-
-
-
-
Re: How to calculate moving avergae
Hi Tomer,
Try this load script out. It will generate the moving average in the load scrip
//---------------------------------------------------------------------------------------------------------------------------------------------------
Moving_Average:
LOAD run_id,
process,
usage,
run_id & '|' & process & '|' & usage as %validation_key
FROM
[Moving Average.xlsx]
(ooxml, embedded labels, table is Sheet1) ;
MA_calc:
Load * ,
if( Peek(process, -1)=process and Peek(run_id, -1) = run_id
and Peek(process, -2)=process and Peek(run_id, -2) = run_id
and Peek(process, -3)=process and Peek(run_id, -3) = run_id
and (not IsNull(Peek(process, -3))) and (not IsNull(Peek(process, -2)))
and (not IsNull(Peek(process, -1)))
, (Peek(usage, -1) + Peek(usage, -2) + Peek(usage, -3) + usage)/4 ,
'NA') as mvg_avg,
'' as test_flag
Resident Moving_Average Order By run_id,process ;
Drop Table Moving_Average;
//---------------------------------------------------------------------------------------------------------------------------------------------------
