topic Re: How to make an average excluding the 2% biggest values ? in QlikView

How to make an average excluding the 2% biggest values ?

fredericvillemi — Wed, 25 Nov 2020 16:16:04 GMT

Hello, I have a list of incidents with a duration and i would like to make an average but excluding the 2% biggest values

for example i have :

duration / in scope

100 1

50 1

1000 1

3 0

3000 0

and i make a sum(duration) / sum(scope)

but i want to exclude the 2% biggest values of duration. How can I do ? If i remove the 3000 I also need to take into account that it was 0 in scope .. I presume i can do it with a aggr but i can't find

Thanks in advance

Re: How to make an average excluding the 2% biggest values ?

sergio0592 — Tue, 10 Mar 2020 14:40:06 GMT

Hi,

For exclude the biggest value in the average calculation, you can use (i suppose you have a key field) :

=sum({<Key-={"=sum(Duration)=max(TOTAL Duration)"} >} Duration) / sum({<Key-={"=sum(Duration)=max(TOTAL Duration)"} >} Scope)

Re: How to make an average excluding the 2% biggest values ?

fredericvillemi — Mon, 23 Mar 2020 16:07:20 GMT

I think my case is more complicated. Your example works in the example i had given but i can't make it work in a more complicated version.

I have a list of incidents (with unique ids) and i want to make the average duration of incidents only if they don't belong to the 2% longest of the Month.

I have managed to do something by precalculating the fractile(0.98) of the month but i don't see how to do without precalculation ..