Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

tomeralon
New Contributor

How to calculate moving avergae

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:

Processrun_idMemory usageMoving average
115
213
126
229

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.

11 Replies
Digvijay_Singh
Honored Contributor III

Re: How to calculate moving avergae

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

Digvijay_Singh
Honored Contributor III

Re: How to calculate moving avergae

See if this helps -

tomeralon
New Contributor

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.

Digvijay_Singh
Honored Contributor III

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?

Digvijay_Singh
Honored Contributor III

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.

Digvijay_Singh
Honored Contributor III

Re: How to calculate moving avergae

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

zebhashmi
Valued Contributor

Re: How to calculate moving avergae

where is the moving data being store?

dwforest
Valued Contributor

Re: How to calculate moving avergae

vikraant
Contributor

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

Community Browser