I have a user who is trying to calculate the percentile of a measure object (Score). Having some problems getting this to work as expected.
I have not really found anything that will help, but it seems to be a context problem since we are getting the #computation error in the field.
User has defined a dimension variable that converts a string “score” to a number using
ToNumber(TextScore)
They have then defined another variable to calculate the perentile using
=Percentile(<Score> , 0.1)
First, how is percentile calculated, I could not find anything in either the Users Guide or Online help, nor the KX or this forum. (At least nothing that I followed )
Next how would I go about trying to determine what the percentile is for a block containing 100s of rows with multi blocks in the report needing different percentiles (2 master dimensions are used creating about 20 blocks).
Environment:
Business Objects 5.1.6 / ZABO Deployment
DB is Sybase ( only have the query results available to me at this stage)
B) BO help text:
Syntax
Number Percentile(number; percentile value)
Description
Returns the percentile of a given set of numeric values. Logical and empty values are ignored, zero is included.
Example
Object=1,2,3,4; Percentile(Object,0.3) returns 1.9
Remark
The extended syntax for this aggregate function allows you to further delimit the context on which the function is to be applied. The context can be specified with In, ForEach, or ForAll operators.
Thanks Andrea. I had read the User Guide, but cannot get the percentile function to work.
I have the following setup:
Category 1 - Main Section
Category 2- Sub Section
Client ID Score
1234 99
5678 99
8476 24
8756 78
6754 65
Then some other blocks for the different categories
If I add a column for percentile using the formula
=percentile(<score>,0.1) in Block
I get a #computation error telling me I have context problems.
if i use
=percentile(<score>,0.1)
I just get the score value.
How do I use the percentile function to return the 10th percentile value.
The help in BO gives an example as you stated but reasoning behind it. The information you linked me has the definition, indicating what a percentile is, but no mathematical calculation to show me what is happening and how to get the right answer. The user wants to know what the “score” is where 90% of the “scores” are equal or higher. I understand that that is what the percentile does, but cannot make it work.
Thought I would add the way we manged to get this working.
What I discovered (can’t believe i missed it!) was that the Percentile function only works with measures. The Variable was a dimension. However the dimension variable is necessary as it is a conversion from a string.
So we need to create a new variable that is a measure of the dimension.
Or this series of variables.
Orginal Object - Actual Score - String - EG “99” or “58”
Score Variable - Dimension
=ToNumber(<Actual Score>)
NewScore - Measure
=<Score>
Percentile Object - Measure
=Percentile(<NewScore>,0.1) in Block
Then add the NewScore (Measure) variable to the table with the Score Variable (Dimension) and hide the Dimension from the Pivot in the Format Table options. When this is don the percentile function not only works, but is shown for each row. This means it can be measured against the results of each row. Voila, exactly what we needed.
I have a question regarding percentile. How can I filter out ONLY the top x% using the Percentile function. I was able to get the actual percentile numbers, but I am unable to filter out just the top x% I’m looking for. When I go to filter, I get “the list of values is empty.” Can anyone help?
Regarding to this percentile function, anyone know how to apply this in WebIntelligence. Or is there any workaround to work out either in universe or SDK, please let me know how?
To get .95 percentile use
Percentile([measure]; .95)
but if you are looking for the 95% confidence interval then that = 1.96 * standard deviation for the normally distributed set
In WebI Percentile expects measures and will generate computational errors if supplied with dimensions. However percentiles can only be generated from dimensions. When using measures BO Percentile gives incorrect results.
)
(BOB member since 2002-08-15)
(BOB member since 2002-06-20)
The Variable was a dimension. However the dimension variable is necessary as it is a conversion from a string.
(BOB member since 2005-12-16)
(BOB member since 2017-07-11)