95th percentile calculation

Hi *,

For our needs, we have to integrate on a Visual Studio project (a Report Server Project, which gives a report dealing with AVG, MAX counters' values from a SQL DB), a calculation of a '95th percentile' average for a counter (in place of a 'normal' average).
(In another words, we have to automatically substract the 5th 'highest values' of the total of the value, and so only make the average on the remaining 95th of the value).
And this treatment should appear as a function, that we could integrate on the Report.

Does this function already exist ?

Does anyone have any idea to help me to find how to do ?

Tks a lot to everybody.

HI,snakeoli :

Can you elaborate more about your requirment? Do you need something like top 5 percent?

|||

Hi,

No, in fact, we have to calculate the average, in the case for several ranges of values.
But to have 'relevant' results, for each sorted ranges of values, we have to exclude 5% of the highest values before calculating the average.
This maneer is called '95th percentile calculation'.

(for ex : in this range of 20 values : 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20
the 95th percentile give : 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19 !!
=> exclusion of the 5thhighest values of the list (5% of '20' gives '1' ==> 1 value)
And so, calculation of the average only on 19 values !)

The goal is, in some case, to supress values that can influence the result of a average.

Imagine that in this example, we have '1250' in the place of '20' !

So the average of the 20 values gives a result not relevant !
(1+ 2+ 3+ 4 + ...+ 18 + 19 + 1250) / 20

And so, finaly, that what we need to create, throught Visual Studio :
a function (similar as 'AVG()') that can calculate automaticaly this !
(for a sorted range of value, a average excluding the 5th highest values !)

I whisper sincerely that I was clearly in my explanation.

Thanks a lot for your help.

|||

Hi,snakeoli:

I got to know what you want to do in this scenario. However AVG() can not deal with such comlicated calculation for you. I just recommend you to do this in the sql statement. It will be much reasonable.