How to calculate moving avergae

[Anonymous]

Hi, I'm new in Qlik. I want to calculate moving average on field called memory per process ID  per run_id for e.g:

Process	run_id	Memory usage	Moving average
1	1	5
2	1	3
1	2	6
2	2	9

How can I calculate it in back stage script or in the QlikSense GUI?
Ultimately I want to display the MAX value of the moving average per process per run_id.

Thanks,
Tomer.

[Digvijay_Singh]

Can you share expected output from the sample data you shared?

[Digvijay_Singh]

See if this helps -

[Anonymous]

Hi,
I've attached an excel to show you what I mean (No pan intended ).
The interval is 4 but can change.

[Digvijay_Singh]

What do you mean by interval, I see initial 3 values are N/A, could you see the one I attached?

[Digvijay_Singh]

Pl see the updated one using latest data, I think rows 29-31 is some typo, as N/A doesn't make sense there.

[Digvijay_Singh]

Also I needed new ID field to generate the output as desired in the sample data.

[zebhashmi]

where is the moving data being store?

[dwforest]

Check out the above function

https://help.qlik.com/en-US/sense/February2018/Subsystems/Hub/Content/ChartFunctions/InterRecordFunc...

[vikraant]

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