Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
Can you share expected output from the sample data you shared?
See if this helps -
Hi,
I've attached an excel to show you what I mean (No pan intended ).
The interval is 4 but can change.
What do you mean by interval, I see initial 3 values are N/A, could you see the one I attached?
Pl see the updated one using latest data, I think rows 29-31 is some typo, as N/A doesn't make sense there.
Also I needed new ID field to generate the output as desired in the sample data.
where is the moving data being store?
Check out the above function
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;
//---------------------------------------------------------------------------------------------------------------------------------------------------