-
Re: How to calculate moving avergae
Digvijay Singh Feb 14, 2018 11:28 AM (in response to Tomer Alon)Can you share expected output from the sample data you shared?
-
Re: How to calculate moving avergae
Digvijay Singh Feb 14, 2018 11:41 AM (in response to Digvijay Singh )See if this helps -
-
291348 - moving average.qvf 192.0 K
-
Re: How to calculate moving avergae
Tomer Alon Feb 15, 2018 11:53 AM (in response to Digvijay Singh )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
Digvijay Singh Feb 15, 2018 1:15 PM (in response to Tomer Alon)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
Digvijay Singh Feb 15, 2018 1:32 PM (in response to Tomer Alon)Pl see the updated one using latest data, I think rows 29-31 is some typo, as N/A doesn't make sense there.
-
291348 - moving average.qvf 192.0 K
-
Re: How to calculate moving avergae
Digvijay Singh Feb 15, 2018 1:33 PM (in response to Digvijay Singh )Also I needed new ID field to generate the output as desired in the sample data.
-
Re: How to calculate moving avergae
Tomer Alon Feb 18, 2018 6:33 AM (in response to Digvijay Singh )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
Digvijay Singh Feb 19, 2018 12:32 AM (in response to Tomer Alon)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
Jahanzeb Hashmi Feb 15, 2018 3:43 PM (in response to Tomer Alon)where is the moving data being store?
-
-
Re: How to calculate moving avergae
Vikraant Pai Feb 16, 2018 2:12 AM (in response to Tomer Alon)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;
//---------------------------------------------------------------------------------------------------------------------------------------------------