Qlik Community

Ask a Question

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
Our May 2021 end-to-end product release from Data Integration to Data Analytics is out! READ DETAILS
cancel
Showing results for 
Search instead for 
Did you mean: 
tomeralon
Contributor
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
Master III
Master III

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

Digvijay_Singh
Master III
Master III

See if this helps -

tomeralon
Contributor
Contributor
Author

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

Digvijay_Singh
Master III
Master III

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

Digvijay_Singh
Master III
Master III

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
Master III
Master III

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

zebhashmi
Specialist
Specialist

where is the moving data being store?

dwforest
Specialist II
Specialist II

vikraant
Creator
Creator

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