I believe this has been discussed before but I am not yet able to get to our archives due to something in our firewall. Anyway, is there a way to perform a median function within BusinessObjects? I cannot find anyway to do it and I seem to remember it being an Oracle limitation. If anyone could refresh my memory in order to pass the reason on to the powers that be, I would appreciate it!!
In a message dated 98-09-09 16:59:51 EDT, you write:
I believe this has been discussed before but I am not yet able to get to
our archives due to something in our firewall. Anyway, is there a way to perform a median function within BusinessObjects? I cannot find anyway to do it and I seem to remember it being an Oracle limitation. If anyone could refresh my memory in order to pass the reason on to the powers that be, I would appreciate it!!
Thanks!!
Maria D. Carter
Ah, but it is not an Oracle limitation… it’s a SQL limitation. Median requires an “ordering” of rows, and SQL is inherantly “unordered”. Before you can find the middle value (median) you have to have ordered rows. If you use an ORDER BY clause, then there is no way to go back to the middle of the list and pick out the middle value.
Having said that, there are some interesting (and potentially nasty from a performance standpoint) tricks with sub-queries that can be used to determine a median. It involves logic like:
Select a value
Count the number of values higher than the value Count the number of values lower than the value When the two count values are equal (same number higher as lower) then you have found your median
Having said that, there are some interesting (and potentially nasty from a performance standpoint) tricks with sub-queries that can be used to determine a median. It involves logic like:
Select a value
Count the number of values higher than the value Count the number of values lower than the value When the two count values are equal (same number higher as lower) then you have found your median
There is another way, the one which was suggested already by someone on this list:if you display your data in a table and number them from 1 to n:
then the median is the value of the row no (n+1)/2 if n is odd, or, the [value of row no (n/2) + value of row no (n/2+1)] / 2 if n is even.
I remember, somebody did this with the “rownumber” function …
Hope this helps
DI Walter Muellner
Delphi Software GmbH, Vivenotgasse 48, A-1120 Vienna / Austria Tel: +43-1-8151456-12, Fax: +43-1-8151456-21 e-mail: w.muellner@delphi.at, WEB: http://www.delphi.at
Business Objects 5.1 does not report medians correctly “in context”. It incorrectly reports the sum.
According to the table shown below the median of the DATA column should be 2 not 6 as shown in the MEDIAN column.
I used “=median(} in TOOL” as the Business Objects function.
Business Objects Support is aware of this but in the meantime I need the median to work “in context” so that I can make a graph of the data table shown below.
Does anyone have any ideas?
TOOL DATA MEDIAN
A 1 6
A 2 6
A 3 6
replace In TOOL by In Body and I think you should be fine, this will enveloppe your context in your report if I’m correct
My2cts
Lode
^~^~^~^~^~^~^~^~^~^~^~^~^~^~^~^~^~^~^~^~^~^~^~^~^~ Lode Van Beethoven
Vesalius - 409 Data Warehouse
Lode.VanBeethoven@minsoc.fed.be
tel: +32 (0)2 210 49 65
prive: lode.van.beethoven@pandora.be
tel: +32 (0)9 329 55 19
cell:+32 (0)485 73 81 02
^~^~^~^~^~^~^~^~^~^~^~^~^~^~^~^~^~^~^~^~^~^~^~^~^~ This transmission is for the intended addressee only and is confidential information. If you have received this transmission in error, please delete it and notify the sender. The contents of this E-mail are the opinion of the writer only and are not endorsed by the Ministery of Public Health unless expressly stated otherwise.
Nothing destroys confidence in your data warehouse project faster than giving the CEO or CFO the wrong answer. “Success is a process, not just a result” “An expert is a man who never makes small mistakes.” - Tom Phipps "Those who can, do.
"Those who can’t, teach.
"Those who can’t teach, rule. (Free after George Bernard Shaw)
From: olmer,leonard j [SMTP:olmer@AGERE.COM] Sent: Monday 30 July 2001 15:27
Business Objects 5.1 does not report medians correctly “in context”. It incorrectly reports the sum.
According to the table shown below the median of the DATA column should be 2
not 6 as shown in the MEDIAN column.
I used “=median(} in TOOL” as the Business Objects function.
Business Objects Support is aware of this but in the meantime I need the median to work “in context” so that I can make a graph of the data table shown below.
Does anyone have any ideas?
TOOL DATA MEDIAN
A 1 6
A 2 6
A 3 6
Any help would be appreciated.
Len Olmer
Agere Systems
mobile phone: 407-448-6576
email: olmer@agere.com
“Success is a process, not just a result” “An expert is a man who never makes small mistakes.” - Tom Phipps "Those who can, do.