80th Percentile
kidkosmo
I have an Access report that lists the number of incidents closed and
the turnaround time (TAT) for each ticket. In the grouping footer, I
have the daily average TAT for the individual and the entire group as
a whole; however, the boss is now looking to display the 80th
percentile. I've looked through other posts and have found references
to add-in programs for purchase to accomplish this task, but my
company does not support those types of purchases. Does anyone have a
VBA function set up to mimic the PERCENTILE function in Excel that
they're willing to share?
Many thanks!
Maarkr
lookup fields on your form, you could build it youself:
sample data...list and rank...formula shows 50th percentile
Value Rank
2 1
3 2
5 3
9 4
R = 100/50 (th percentile) = 2
IR = First Rank (1) + Last Rank (4) = 5
FR = 50 (th percentile) / 100 = .5
IR/R is rank of 50th pctl = 5 / 2= 2.5
Rank of 2.5 = low value of 3 and high value of 5
Percentile Value = FR(HV-LV)+LV
= .5 (5-3)+3 = 4
person with value of 4 is at 50th percentile
don't know if this helps or confuses things more?
James A. Fortune
Maybe:
http://groups.google.com/group/comp.databases.ms-access/browse_frm/thread/1789b9f773475c5a
Post back if you need something more like the PERCENTILE function in Excel.
James A. Fortune
MPAP...@FortuneJames.com
kidkosmo
wrote:
> kidkosmo wrote:
> > Hi, Gang,
>
> > I have an Access report that lists the number of incidents closed and
> > the turnaround time (TAT) for each ticket. In the grouping footer, I
> > have the daily average TAT for the individual and the entire group as
> > a whole; however, the boss is now looking to display the80th
> >percentile. I've looked through other posts and have found references
> > to add-in programs for purchase to accomplish this task, but my
> > company does not support those types of purchases. Does anyone have a
> > they're willing to share?
>
> > Many thanks!
>
> Maybe:
>
>
> Post back if you need something more like thePERCENTILEfunction in Excel.
>
> James A. Fortune
> MPAPos...@FortuneJames.com
I do need something more like the Percentile function in Excel. I've
tried to incorporate the previous post, but I'm not really able to get
it to work. Any further assistance would be great!
James A. Fortune
> I do need something more like the Percentile function in Excel. I've
> tried to incorporate the previous post, but I'm not really able to get
> it to work. Any further assistance would be great!
kidkosmo,
How about this:
'-------Begin Module Code -------
Public Function PERCENTILE(dblArray() As Double, intArrayUBound As
Integer, K As Double) As Double
Dim dblIndex As Double
Dim I As Integer
Dim IMax As Integer
Dim dblWorkingArray() As Double
Dim P() As Integer
PERCENTILE = CDbl(0)
If intArrayUBound < UBound(dblArray()) Then
IMax = intArrayUBound
Else
IMax = UBound(dblArray())
End If
ReDim dblWorkingArray(IMax)
ReDim P(IMax)
For I = 0 To IMax
dblWorkingArray(I) = dblArray(I)
Next I
'Input Validation
If UBound(dblWorkingArray()) < 1 Then
MsgBox ("Invalid input. There must be at least two relative standing
values.")
Exit Function
End If
If UBound(dblWorkingArray()) > 29999 Then
MsgBox ("Invalid input. Over 30,000 relative standing values.")
Exit Function
End If
If K < 0 Or K > 1 Then
MsgBox ("Invalid input. K must be between 0 and 1 inclusive.")
Exit Function
End If
'Calculate array of pointers
Call DoArraySort(dblWorkingArray(), P(), IMax)
dblIndex = UBound(dblWorkingArray) * K
If Abs(Int(dblIndex) - dblIndex) < 0.000001 Then
PERCENTILE = dblWorkingArray(P(Int(dblIndex)))
Else
'Use linear interpolation
PERCENTILE = (dblWorkingArray(P(Int(dblIndex) + 1)) -
dblWorkingArray(P(Int(dblIndex)))) * (dblIndex - Int(dblIndex)) +
dblWorkingArray(P(Int(dblIndex)))
End If
End Function
Private Sub DoArraySort(dblWorkingArray() As Double, ByRef P() As
Integer, IMax As Integer)
Dim I As Integer
Dim J As Integer
Dim NumBelow As Integer
Dim NumTied As Integer
'Use P() to point from the (rank - 1) of the array element from lowest
to highest
'instead of moving the values around within the array
For I = 0 To IMax
NumBelow = 0
NumTied = -1
For J = 0 To IMax
If dblWorkingArray(J) = dblWorkingArray(I) Then
If J >= I Then
NumTied = NumTied + 1
End If
ElseIf dblWorkingArray(J) < dblWorkingArray(I) Then
NumBelow = NumBelow + 1
End If
Next J
'Note: The pointer must point backwards
P(NumBelow + NumTied) = I
Next I
End Sub
'-------End Module Code -------
Sample usage:
'---Begin Code behind Form
Private Sub cmdGo_Click()
Dim X(5) As Double
Dim K As Double
X(0) = 8
X(1) = 7
X(2) = 6
X(3) = 5
X(4) = 4
X(5) = 3
K = 0.51
MsgBox (PERCENTILE(X, 5, K))
End Sub
'---End Code behind Form
Note: If PERCENTILE(X, 4, K) is used then the X(5) = 3 will be ignored
in the calculation.
Result: 5.55 (Same as Excel's =PERCENTILE({8, 7, 6, 5, 4, 3}, 0.51)
Some other cases I tried:
1, 2, 3, 4 K = 0.3 => 1.9 (Same as Excel's =PERCENTILE({1, 2, 3, 4},
0.3)) Note: intArrayUBound = 3, not 4
1, 2, 3, 4, 5 K = 0.3 => 2.2 (Same as Excel's =PERCENTILE({1, 2, 3, 4,
5}, 0.3))
1, 2, 3, 4, 5 K = 0.49 => 2.96 (Same as Excel's =PERCENTILE({1, 2, 3, 4,
5}, 0.49))
-1, 0, 1 K = 0.3 => -0.4 (Same as Excel's =PERCENTILE({-1, 0, 1}, 0.3))
-1, 0, 1 K = 0.51 => 0.02 (Same as Excel's =PERCENTILE({-1, 0, 1}, 0.51))
1, 1, 1, 2, 3, 5 K = 0.3 => 1 (Same as Excel's =PERCENTILE({1, 1, 1, 2,
3, 5}, 0.3)
3, 1, 5, 1, 2, 1 K = 0.3 => 1 (Same as Excel's =PERCENTILE({3, 1, 5, 1,
2, 1}, 0.3)
Be sure to test this PERCENTILE function thoroughly before use. I tried
to include handling relative standing values that are tied and that are
not in order so test those cases also. I hope that will get you
started. I tried using the Array() function to pass in a Variant before
eventually using a real array so you can try that if you want the
function to look even more like Excel's PERCENTILE function. If you're
very careful about resizing your array you can also get around using
intArrayUBound as an argument and the code associated with it. Then
you're really close to the looks of the Excel function. Also, watch out
for line wrap. Red text showing up in code is a sign that the newsgroup
put in more carriage returns than I intended.
James A. Fortune
MPAP...@FortuneJames.com
kidkosmo
wrote:
> kidkosmo wrote:
> > I do need something more like thePercentilefunction in Excel. I've
> > tried to incorporate the previous post, but I'm not really able to get
> > it to work. Any further assistance would be great!
>
> kidkosmo,
>
> How about this:
>
> '-------Begin Module Code -------
> Integer, K As Double) As Double
> Dim dblIndex As Double
> Dim I As Integer
> Dim IMax As Integer
> Dim dblWorkingArray() As Double
> Dim P() As Integer
>
> Else
> 'Use linear interpolation
> PERCENTILE= (dblWorkingArray(P(Int(dblIndex) + 1)) -
> in the calculation.
>
> Result: 5.55 (Same as Excel's =PERCENTILE({8, 7, 6, 5, 4, 3}, 0.51)
>
> Some other cases I tried:
> 1, 2, 3, 4 K = 0.3 => 1.9 (Same as Excel's =PERCENTILE({1, 2, 3, 4},
> 0.3)) Note: intArrayUBound = 3, not 4
> 1, 2, 3, 4, 5 K = 0.3 => 2.2 (Same as Excel's =PERCENTILE({1, 2, 3, 4,
> 5}, 0.3))
> 1, 2, 3, 4, 5 K = 0.49 => 2.96 (Same as Excel's =PERCENTILE({1, 2, 3, 4,
> 5}, 0.49))
> -1, 0, 1 K = 0.3 => -0.4 (Same as Excel's =PERCENTILE({-1, 0, 1}, 0.3))
> -1, 0, 1 K = 0.51 => 0.02 (Same as Excel's =PERCENTILE({-1, 0, 1}, 0.51))
> 1, 1, 1, 2, 3, 5 K = 0.3 => 1 (Same as Excel's =PERCENTILE({1, 1, 1, 2,
> 3, 5}, 0.3)
> 3, 1, 5, 1, 2, 1 K = 0.3 => 1 (Same as Excel's =PERCENTILE({3, 1, 5, 1,
> 2, 1}, 0.3)
>
> to include handling relative standing values that are tied and that are
> not in order so test those cases also. I hope that will get you
> started. I tried using the Array() function to pass in a Variant before
> eventually using a real array so you can try that if you want the
> very careful about resizing your array you can also get around using
> intArrayUBound as an argument and the code associated with it. Then
> you're really close to the looks of the Excel function. Also, watch out
> for line wrap. Red text showing up in code is a sign that the newsgroup
> put in more carriage returns than I intended.
>
> James A. Fortune
This is GREAT stuff! Where I'm getting stuck now is creating my array
(another foreign territory for me) and how to resize the array. My
array is going to be dynamic based on the date range selected. What
I've got so far for my SQL statement is "SELECT TAT from
tblTEMP_CR_Calc" to get my recordset.
Also, is there any way to incorporate this calculation into a report?
I've got a report that uses all the data from tblTEMP_CR_Calc but has
grouping levels by site. Here's kinda how it looks right now:
Site # TAT %<2 Days
Denver 25 2.5 70%
Las Vegas 30 1.5 80%
What we're hoping to get is:
Site # TAT %<2 Days 80th Percentile
Denver 25 2.5 70% 1.25
Las Vegas 30 1.5 80% 1.09
Would this even be possible to do on a report or would it be better to
try to incorporate this into a crosstab query and display it that way?
Again, thank you so much for your help!
James A. Fortune
> This is GREAT stuff! Where I'm getting stuck now is creating my array
> (another foreign territory for me) and how to resize the array. My
> array is going to be dynamic based on the date range selected. What
> I've got so far for my SQL statement is "SELECT TAT from
> tblTEMP_CR_Calc" to get my recordset.
I see that Ken posted a good way for you to get your recordset into an
array in another thread.
>
> Also, is there any way to incorporate this calculation into a report?
> I've got a report that uses all the data from tblTEMP_CR_Calc but has
> grouping levels by site. Here's kinda how it looks right now:
>
> Site # TAT %<2 Days
> Denver 25 2.5 70%
> Las Vegas 30 1.5 80%
>
> What we're hoping to get is:
>
> Site # TAT %<2 Days 80th Percentile
> Denver 25 2.5 70% 1.25
> Las Vegas 30 1.5 80% 1.09
>
> Would this even be possible to do on a report or would it be better to
> try to incorporate this into a crosstab query and display it that way?
>
> Again, thank you so much for your help!
Perhaps have very short invisible text boxes in the detail section that
contain the data used for your site groupings? I'm not sure where you
would run the code that puts the data into an array in such a case or
even in the crosstab case. I think I might change the function to
accept a SQL string along with a separate K value. Each SQL string,
sent in at the group level, would contain the particular Site for the
current group as part of the string. Something like (K = 0.31):
=GetPercentile("SELECT X FROM tblTEMP_CR_Calc WHERE Site = " & Chr(34) &
txtSite.Value & Chr(34) & ";", 0.31)
or
=GetPercentile("SELECT X FROM tblTEMP_CR_Calc WHERE Site = " & Chr(34) &
[Site] & Chr(34) & ";", 0.31)
or
=GetPercentile("SELECT X FROM tblTEMP_CR_Calc WHERE SiteNumber = " &
[SiteNumber] & ";", 0.31)
Then you could use Ken's idea to put the SQL data into an array to use
as input to the original function?
James A. Fortune
MPAP...@FortuneJames.com
kidkosmo
wrote:
>
> - Show quoted text -
I'll keep trying to play with what you last posted; however, I've
still having a dilemma with capturing my array and using in the
Percentile function. I've set up the code below, but I get an error
at the Percentile function that states, "Type mismatch: array or user-
defined type expected".
Dim rs As DAO.Recordset
Dim db As DAO.Database
Dim strSQL As String
Dim aTAT()
Dim lCount As Long
strSQL = "SELECT TAT from tblTEMP_CR_Calc"
Set db = CurrentDb
Set rs = db.OpenRecordset(strSQL)
aTAT = rs.GetRows()
lCount = rs.RecordCount
MsgBox (PERCENTILE(aTAT, lCount, 0.8))
James A. Fortune
Sending a Long when the function expects an Integer or sending a Variant
array when the function expects a Double array can cause problems.
Access functions are not as smart as you think they are :-). Try
changing that first.
James A. Fortune
MPAP...@FortuneJames.com
Martin Luther King Jr. was prophetic in his "I have a dream" speech.
Equality is now here. We've all been given a check marked "insufficient
funds." :-)
kidkosmo
>
> Martin Luther King Jr. was prophetic in his "I have a dream" speech.
> Equality is now here. We've all been given a check marked "insufficient
>
> - Show quoted text -
I did try changing my declarations, but when I did that I get a Type
Mismatch error on the "aTAT=rs.GetRow()" line. I do have the TAT
field of the table defined as Double as well. I'm so cornfused. :~)
Dim rs As DAO.Recordset
Dim db As DAO.Database
Dim strSQL As String
Dim aTAT() As Double
Dim lCount As Integer
strSQL = "SELECT TAT from tblTEMP_CR_Calc"
Set db = CurrentDb
Set rs = db.OpenRecordset(strSQL)
If Not rs.BOF Then rs.MoveFirst
aTAT = rs.GetRows()
lCount = rs.RecordCount
MsgBox PERCENTILE(aTAT, lCount, 0.8)
James A. Fortune
> aTAT = rs.GetRows()
>
> lCount = rs.RecordCount
>
> MsgBox PERCENTILE(aTAT, lCount, 0.8)
Obviously, assigning a Variant to a Double can also cause problems.
Perhaps an auxiliary Variant array {vTAT()?} can be used to facilitate
the GetRows part, with a second step, perhaps involving the CDbl()
function, being used to convert the vTAT() Variants into aTAT() Doubles.
Use the NZ() function inside the CDbl() function if you want Nulls to
be treated like zeroes. Exclude Nulls with your SQL statement if you
don't want to consider them.
James A. Fortune
MPAP...@FortuneJames.com
kidkosmo
wrote:
> kidkosmo wrote:
> > aTAT = rs.GetRows()
>
> > lCount = rs.RecordCount
>
>
> Obviously, assigning a Variant to a Double can also cause problems.
> Perhaps an auxiliary Variant array {vTAT()?} can be used to facilitate
> the GetRows part, with a second step, perhaps involving the CDbl()
> function, being used to convert the vTAT() Variants into aTAT() Doubles.
> Use the NZ() function inside the CDbl() function if you want Nulls to
> be treated like zeroes. Exclude Nulls with your SQL statement if you
> don't want to consider them.
>
> James A. Fortune
Any other tricks up your sleeve? I tried using the CDbl() as shown
below, but get the same Type Mismatch error on the variant in the aTAT
= CDbl(vTAT) line. Since I'm plugging data into a temp table, I'm
already removing any null values, so I didn't bother adding the NZ()
function.
BTW...thank you SO much for all of the help you've been providing. If
nothing else, I am learning more than I ever intended. :)
Dim strSQL As String
Dim vTAT()
Dim aTAT() As Double
Dim lCount As Integer
strSQL = "SELECT TAT from tblTEMP_CR_Calc"
Set db = CurrentDb
Set rs = db.OpenRecordset(strSQL)
vTAT = rs.GetRows()
aTAT = CDbl(vTAT)
James A. Fortune
> Any other tricks up your sleeve? I tried using the CDbl() as shown
> below, but get the same Type Mismatch error on the variant in the aTAT
> = CDbl(vTAT) line. Since I'm plugging data into a temp table, I'm
> already removing any null values, so I didn't bother adding the NZ()
> function.
>
> BTW...thank you SO much for all of the help you've been providing. If
> nothing else, I am learning more than I ever intended. :)
>
> Dim strSQL As String
> Dim vTAT()
> Dim aTAT() As Double
>
> Dim lCount As Integer
>
> strSQL = "SELECT TAT from tblTEMP_CR_Calc"
>
> Set db = CurrentDb
> Set rs = db.OpenRecordset(strSQL)
>
> vTAT = rs.GetRows()
>
> aTAT = CDbl(vTAT)
kidkosmo,
I think you're just stringing me along by pretending not to know
anything, but either way, perhaps this will help someone. From the A97
help file on the GetRows Method:
(begin quote)
Retrieves multiple rows from a Recordset object.
Syntax
Set varArray = recordset.GetRows (numrows)
The GetRows method syntax has the following parts.
Part Description
varArray A Variant that stores the returned data.
recordset An object variable that represents a Recordset object.
numrows A Variant that is equal to the number of rows to retrieve.
Remarks
Use the GetRows method to copy records from a Recordset. GetRows returns
a two-dimensional array. The first subscript identifies the field and
the second identifies the row number. For example, intField represents
the field, and intRecord identifies the row number:
avarRecords(intField, intRecord)
To get the first field value in the second row returned, use code like
the following:
field1 = avarRecords(0,1)
To get the second field value in the first row, use code like the following:
field2 = avarRecords(1,0)
The avarRecords variable automatically becomes a two-dimensional array
when GetRows returns data.
If you request more rows than are available, then GetRows returns only
the number of available rows. You can use the Visual Basic for
Applications UBound function to determine how many rows GetRows actually
retrieved, because the array is sized to fit the number of returned
rows. For example, if you returned the results into a Variant called
varA, you could use the following code to determine how many rows were
actually returned:
numReturned = UBound(varA,2) + 1
You need to use "+ 1" because the first row returned is in the 0 element
of the array. The number of rows that you can retrieve is constrained by
the amount of available memory. You shouldn't use GetRows to retrieve an
entire table into an array if it is large.
Because GetRows returns all fields of the Recordset into the array,
including Memo and Long Binary fields, you might want to use a query
that restricts the fields returned.
After you call GetRows, the current record is positioned at the next
unread row. That is, GetRows has the same effect on the current record
as Move numrows.
If you are trying to retrieve all the rows by using multiple GetRows
calls, use the EOF property to be sure that you're at the end of the
Recordset. GetRows returns less than the number requested if it's at the
end of the Recordset, or if it can't retrieve a row in the range
requested. For example, if you're trying to retrieve 10 records, but you
can't retrieve the fifth record, GetRows returns four records and makes
the fifth record the current record. This will not generate a run-time
error. This might occur if another user deletes a record in a
dynaset-type Recordset. See the example for a demonstration of how to
handle this.
(end quote)
The Example:
(begin quote)
The following example uses the GetRows method to return a
two-dimensional array containing all rows of data in a Recordset object:
Sub RowsArray()
Dim dbs As Database, rst As Recordset, strSQL As String
Dim varRecords As Variant, intI As Integer, intJ As Integer
' Return reference to current database.
Set dbs = CurrentDb
' Build SQL statement that returns specified fields.
strSQL = "SELECT FirstName, LastName, HireDate " _
& "FROM Employees"
' Open dynaset-type Recordset object.
Set rst = dbs.OpenRecordset(strSQL)
' Move to end of recordset.
rst.MoveLast
' Return to first record.
rst.MoveFirst
' Return all rows into array.
varRecords = rst.GetRows(rst.RecordCount)
' Find upper bound of second dimension.
For intI = 0 To UBound(varRecords, 2)
Debug.Print
' Find upper bound of first dimension.
For intJ = 0 To UBound(varRecords, 1)
' Print data from each row in array.
Debug.Print varRecords(intJ, intI)
Next intJ
Next intI
rst.Close
Set dbs = Nothing
End Sub
(end quote)
What it boils down to is that you need to assign the Double values in a
loop while addressing the specific field that you want. An interesting
possibility that the GetRows Method might enable would be for using SQL
to pull a specific data range from an Excel spreadsheet into such a
variant array, excluding certain rows with the WHERE part, provided the
resulting number of rows/records from the spreadsheet are not too large
for the array. 'aTAT = CDbl(vTAT)' or 'aTAT = CDbl(Nz(vTAT))' or even
'vTAT() = aTAT().ConvertToDouble' or 'vTAT() = aTAT().NzConvertToDouble'
would have been a nice feature in Access, but it simply is not there.
So instead of being able to do the most intuitive thing (as was your
guess), we are forced to loop through the elements. It's not a major
issue, though, since it can be done in a few lines of code. Post back
if you need help in creating that loop.
James A. Fortune
MPAP...@FortuneJames.com
kidkosmo
wrote:
>
> - Show quoted text -
As you guessed it, James, I do need help with that loop. I've made a
first go at it, but continue to get the type mismatch error.
p.s. I'm not string you along...I really don't know anything, and I'm
not afraid to say it. :~) All of this is outside of my VBA skillset
(limited as it is).
kidkosmo
wrote:
>
> - Show quoted text -
So...I may have made some headway with this:
Dim rs As DAO.Recordset
Dim db As DAO.Database
Dim strSQL As String
Dim varField As Variant
Dim aTAT() As Double
Set db = CurrentDb
strSQL = "SELECT TAT from tblTEMP_CR_Calc"
Set rs = db.OpenRecordset(strSQL)
rs.MoveLast
Debug.Print rs.RecordCount
ReDim aTAT(rs.RecordCount, rs.Fields.Count)
rs.MoveFirst
Do While Not rs.EOF
For Each varField In rs.Fields
aTAT(rs.AbsolutePosition, varField.OrdinalPosition) =
varField
Debug.Print varField
Next varField
rs.MoveNext
Loop
Debug.Print rs.RecordCount
MsgBox (PERCENTILE(aTAT, rs.RecordCount, 0.8))
But now, I'm getting a subscript out of range on the Percentile
function at the
For I = 0 To IMax
dblWorkingArray(I) = dblArray(I)
Next I
Thoughts?
James A. Fortune
> But now, I'm getting a subscript out of range on the Percentile
> function at the
> For I = 0 To IMax
> dblWorkingArray(I) = dblArray(I)
> Next I
>
> Thoughts?
See:
http://mathworld.wolfram.com/DirichletsBoxPrinciple.html
Basically, if each pigeonhole can contain only one pigeon, you can't put
more than n pigeons in n pigeonholes. You are trying to put IMax + 1
pigeons into IMax pigeonholes. That simple principle turns out to be
quite handy for a range of proofs in number theory. If you are working
in A2K or later, perhaps try using ADO to put your recordset into an
array. Those are my thoughts, as strange as they may seem :-).
James A. Fortune
MPAP...@FortuneJames.com
Bootable DVD or flash drive:
http://blogs.technet.com/shawnt/archive/2007/12/13/how-to-create-a-bootable-dvd-for-windows-vista-or-any-windows-for-that-matter.aspx