average over percentile script

[ale_s]

Hi all, 

I'm quite new to qlik and I falled on a problem I'm not able to solve. Basically I have an excel table with "Date" (from 01/01/2020 to 01/12/2020) and "Value". In excel I firstly manually cycle over the months and for each month I do:

1-order "Value" from smaller to bigger

2-count the number of rows 

3-average "Value" over the first 95% values of "Value" (so for example if i have 100 rows, I should average "Value"over the first 95 rows).

I tried this on qlik but didn't work:

Let varMinDate = NUM(Date#('01/01/2020','DD/MM/YYYY'));
Let varMaxDate = NUM(Date#('01/12/2020','DD/MM/YYYY'));

For i=$(varMinDate) to $(varMaxDate);

tbl:
load
avg(Value) as var1,
Date
resident previous_table
where rowno() < rowno()*0.95
group by Date
order by Value asc;

next i;

can somebody please help, thank you!

[anthonyj · Accepted solution]

Hi @ale_s 

Qlik has a fractile function  that takes 95% as a parameter. I've written a script below that keeps all your data but flags those records that are within the 95th percentile. You can easily remove these if needs be or use a set analysis to filter them out.

AvgPerc:
LOAD
MonthDate,
Value
FROM [lib://AttachedFile/Avg Percentile.qvd](qvd);

//Aggregate the 95th percentile by MonthDate
Percentile:
Load
MonthDate,
Fractile(Value,.95) as Fractile
Resident AvgPerc
group by MonthDate;

// Set the number of times to loop through the months
let vMonthNum = NoOfRows('Percentile');

// Set the concatenation variable to null
let vConcatenate =;

//Loop through each month in the data
for i = 0 to $(vMonthNum)-1

//Obtain the percentile for the relevant month
let vPerc = peek('Fractile',$(i),'Percentile');
let vMonth = peek('MonthDate', $(i), 'Percentile');

$(vConcatenate)

// Flag the records that are equal or less than the percentile for that month
data:
load
MonthDate,
Value,
if(Value <= $(vPerc), 1,0) as Flag
Resident AvgPerc
where MonthDate = '$(vMonth)';

Trace
****************
$(vPerc)
$(vMonth)
$(i)
;
// update the concatenate variable so the next
let vConcatenate = concatenate;

next i;

drop table AvgPerc;

let vMonth=;
let vPerc=;
let vMonthNum=;

I hope this helps.

Thanks 

Anthony