Announcements
The way to achieve your own success is the willingness to help somebody else. Go for it!
cancel
Showing results for
Did you mean:
Creator III

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

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

1 Solution

Accepted Solutions
Specialist III

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)``
2 Replies
Specialist III

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)``
Creator III
Author

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 ..

Community Browser