Median

building semantic layer
1

Hi,

Does anyone know how to calculate the median of a column?

I am using BO 4.1.3 and MS Access 97.

Thanks!

Mike Camillone
camillone@majorleaguebaseball.com

Listserv Archives (BOB member since 2002-06-25)

2

That is tuff!!

To do it right, you need to sort on value, decide if the size of the set is even or odd, and either take the middle element, or take the middle two and divide by 2…

Brent

Hi,

Does anyone know how to calculate the median of a column?

I am using BO 4.1.3 and MS Access 97.

Thanks!

Mike Camillone
camillone@majorleaguebaseball.com

Listserv Archives (BOB member since 2002-06-25)

3

In a message dated Tue, 22 Feb 2000 2:55:51 PM Eastern Standard Time, “Camillone, Michael” camillone@MAJORLEAGUEBASEBALL.COM writes:

Hi,

Does anyone know how to calculate the median of a column?

I am using BO 4.1.3 and MS Access 97.

Thanks!

Mike Camillone
camillone@majorleaguebaseball.com

Mike:

BusObj 5.0.1 has a Median function in the reporter module. You should probably upgrade rather than try to work out some sort of formula on your own.

Regards,
Dave Rathbun
Integra Solutions
www.islink.com

Listserv Archives (BOB member since 2002-06-25)

4

I have been trying and trying to figure this one out, but to no avail. Has anyone been able to do this (aside from upgrading to version 5)?

That is tuff!!

To do it right, you need to sort on value, decide if the size of the set is
even or odd, and either take the middle element, or take the middle two and
divide by 2…

Brent

Hi,

Does anyone know how to calculate the median of a column?

I am using BO 4.1.3 and MS Access 97.

Thanks!

Mike Camillone
camillone@majorleaguebaseball.com

Pls report bounces in response to postings to BUSOB-L-Request@listserv.aol.com
Web archives (24 hrs. a day now!):
listserv.aol.com/archives/busob-l.html
OR search: Mail to listserv@listserv.aol.com, ‘search a_phrase in
BUSOB-L’
Unsubscribe: Mail to listserv@listserv.aol.com, ‘unsubscribe BUSOB-L’
====================================

Pls report bounces in response to postings to BUSOB-L-Request@listserv.aol.com
Web archives (24 hrs. a day now!): listserv.aol.com/archives/busob-l.html
OR search: Mail to listserv@listserv.aol.com, ‘search a_phrase in BUSOB-L’
Unsubscribe: Mail to listserv@listserv.aol.com, ‘unsubscribe BUSOB-L’

Listserv Archives (BOB member since 2002-06-25)

5

Since you are using MSAccess you will need to create another table which will calculate the median values. Then add that table to your Universe(or as a linked table) and link the field via ID or other link. The code for median calculation is below:

  1. Using a query create a temptable*1 that has player and hits. Then run the function Median against the temptable
  2. tName$ is the name of the table and fldName$ is the name of the field pass the variables
  3. =Median(“tblPlayers”,“Hits”) to call the function 4. Next inside a loop you will need to put an export to your table which holds the median information like
    DoCmd.RunSQL “Insert into [MedianHits] (Player, MedianHits) Values (’” & variblename4player & “’,’” & Median & “’)” 5. Loop through processes 1 through 4 for each player 6. Then link the table (MedianHits) to BusinessObjects and run your report.

*1 A temptable must be used since the Median function is table specific not row specific. That’s the reason there is a Median function in Excel but not in Access. If you don’t want to use a temp table then you may use an array. I am too tired to type about Arrays but there is plenty of examples at VBA and Access User Group Web Sites – fyi you will have to ReDim() your array through each loop.

Good Luck
John Vance Sullivan
Carolina Power & Light
P.O. Box 1551
Raleigh, NC 27602-1551
919-546-4466
john.sullivan@cplc.com

Function Median(tName$, fldName$) As Single
Dim MedianDB As Database
Dim ssMedian As Snapshot
Dim RCount%, i%, z%, OffSet%
Dim x#, y#

Set MedianDB = CurrentDb()
Set ssMedian = MedianDB.CreateSnapshot(“SELECT [” & fldName$ & “] FROM [” & tName$ & “] ORDER BY [” & fldName$ & “];”)
If ssMedian.RecordCount = 0 Then Exit Function

ssMedian.MoveLast
RCount% = ssMedian.RecordCount
z% = RCount% Mod 2

If z% <> 0 Then
OffSet% = ((RCount% + 1) / 2) - 2
For i% = 0 To OffSet%
ssMedian.MovePrevious
Next i
Median = ssMedian(fldName$)
Else
OffSet% = (RCount% / 2) - 2
For i% = 0 To OffSet%
ssMedian.MovePrevious
Next i
x# = ssMedian(fldName$)
ssMedian.MovePrevious
y# = ssMedian(fldName$)
Median = (x# + y#) / 2

End If
ssMedian.Close
MedianDB.Close
End Function

Listserv Archives (BOB member since 2002-06-25)

6

Has anyone been able to do this (aside from upgrading to version 5)?
Yes, but only by using an Oracle function to do it. Works fine although I only implemented it about 3 weeks before going to Version 5. Lovely bit of wasted code… :frowning:

Regards

Jonathan

Project Leader
Group Medical, Regulatory and Product Strategy IS.

Listserv Archives (BOB member since 2002-06-25)

7

mrunals (BOB member since 2006-12-27)