85th Percentile of a set of records

[Preacha]

Hey,
I have a table with a group of speeds by date, Like so . . .
24/06/2004 12:00 47
24/06/2004 13:00 64
24/06/2004 14:00 53
25/06/2004 12:00 55
25/06/2004 13:00 55
26/06/2004 12:00 56
26/06/2004 13:00 55
26/06/2004 14:00 45


I want to calculate the 85th percentile for each date (for a specified period of time), of course there are more hours and dates in the original table but the above is just an overview (didn't want to paste 14,000 records up ). Anyway, my query so far is like the follow. . . .

TRANSFORM ((((Sum(SpeedImport.Speed))-1)*0.85)+1)-Int((((Sum(SpeedImport.Speed)-1)*0.85)+1)) AS percentile
SELECT SpeedImport.RecordDate
FROM (Direction INNER JOIN Headers ON Direction.DirectionID = Headers.DirectionID) INNER JOIN SpeedImport ON Headers.RecordNo = SpeedImport.RecordNo
WHERE (((SpeedImport.RecordHour) Between "00" And "23"))
GROUP BY SpeedImport.RecordDate
ORDER BY SpeedImport.RecordDate
PIVOT Headers.SiteNo;

The code after transform is incorrect, I just want to know how I would go about doing it in SQL in access or possibly in VBA.




I want the query to output data like this . . .
ie. between 12:00 and 14:00

RecordDate 85th Percentile
24/06/2004 60.7
25/06/2004 55
26/06/2004 55.7

This is easily done in excel, but not so in access.
Any help would be great guys

 

[WayneRyan]

Preacha,

SQL can "Select TOP 15 PERCENT ..."

Since you can isolate the Top 15 Percent of the recordset, it should be
easy to use them as a group or take the top/bottom "n" values using a
subsequent application of TOP. You may end up nesting/chaining queries,
but it can be done.

I think that's what you're looking for.

Wayne

 

[Mike375]

Preacha

Because I am always on the look out for easy solutions, could you post up that problem in the Excel forum and the easy solution.

Thank you.

Mike

 

[Mile-O]

Could you post up that problem in the Excel forum and the easy solution.

This is not an Excel problem and we don't need extra posts on the same subject cluttering up the forum.

 

[Mike375]

This is not an Excel problem and we don't need extra posts on the same subject cluttering up the forum.

It could mean that the forum is not normalised

 

[Jon K]

Mike,

In Excel, all you need is set a formula.

For example, if the Speed figures for 24/06/2004 are in C2:C4 on an Excel worksheet, the following formula will return the 85th percentile of the figures:-

=PERCENTILE(C2:C4, 0.85)


Preacha,

Though Access doesn't have a Percentile function, if you make a reference to the Microsoft Excel X.X Object Library (when the code window is open, choose menu Tools, References... and select the library), you can then use the Excel function in Access VBA by passing an array of figures to it, e.g.

sngPercentile = WorksheetFunction.Percentile(arraySpeed, 0.85)

Jon

 

[Preacha]

I found code for VBA on how to do it. I Was just wondering how I would implement this so that it groups the speeds by dates

Public Function PercentileRst(RstName As String, fldName As String, PercentileValue As Double) As Double
   'This function will calculate the percentile of a recordset.
   'The field must be a number value and the percentile has to
   'be between 0 and 1.
   If PercentileValue < 0 Or PercentileValue > 1 Then
      MsgBox "Percentile must be between 0 and 1", vbOKOnly
   End If
   
   Dim PercentileTemp As Double
   Dim dbs As Database
   Set dbs = CurrentDb
   Dim xVal As Double
   Dim iRec As Long
   Dim i As Long
   Dim RstOrig As Recordset
   Set RstOrig = CurrentDb.OpenRecordset(RstName, dbOpenDynaset)
   RstOrig.Sort = fldName
   Dim RstSorted As Recordset
   Set RstSorted = RstOrig.OpenRecordset()
   RstSorted.MoveLast
   RstSorted.MoveFirst
   xVal = ((RstSorted.RecordCount - 1) * PercentileValue) + 1
   'x now contains the record number we are looking for.
   'Note x may not be whole number
   iRec = Int(xVal)
   xVal = xVal - iRec
   'i now contains first record to look at and
   'x contains diff to next record
   RstSorted.Move iRec - 1
   PercentileTemp = RstSorted(fldName)
   If xVal > 0 Then
      RstSorted.MoveNext
      PercentileTemp = ((RstSorted(fldName) - PercentileTemp) * xVal) + PercentileTemp
   End If
   RstSorted.Close
   RstOrig.Close
   Set RstSorted = Nothing
   Set RstOrig = Nothing
   Set dbs = Nothing
   PercentileRst = PercentileTemp
End Function

cheers

 

[Preacha]

What if I create a table called "Dates" that lists all the dates and somehow use that to group the 85th Percentiles using those dates? Does anyone know how I go about this?

 

[Preacha]

This TOP 15 Percent thing is intriguing, how would I go about using it?

 

[Preacha]

This is wrong but is this the general idea on how you do it. Btw Wayne thanks for your help with importing files, it works well. I may have some later issues later tho

SELECT  SpeedImport.RecordDate, (SELECT TOP 85 PERCENT SpeedImport.Speed FROM SpeedImport)
FROM Headers INNER JOIN SpeedImport ON Headers.RecordNo = SpeedImport.RecordNo
GROUP BY SpeedImport.RecordDate
ORDER BY SpeedImport.RecordDate;

 

[WayneRyan]

Preacha,

Just a guess here ...

SELECT  SpeedImport.RecordDate, 
        SpeedImport.Speed
FROM    SpeedImport 
Where   SpeedImport.RecordID In 
       (SELECT TOP 85 PERCENT SpeedImport.RecordID
        FROM SpeedImport
        Order by SpeedImport.Speed DESC)
ORDER BY SpeedImport.RecordDate;

Wayne

 

[Preacha]

Yeah, but that doesn't group by Date, if you add

GROUP BY SpeedImport.RecordDate

It comes up with an errror message saying that speed is not part of the aggregate function.
Any Ideas?

 

[WayneRyan]

Preacha,

Yup, I realized that after I posted. Need to do some research here, this is
new turf for me.

I saw Jon K looking at this earlier, hope he has some thoughts, he's very
good with queries.

Let you know in a bit,
Wayne

 

[WayneRyan]

Preacha,

I think this is it.

SELECT A.TheDate, 
             A.RecordID, 
             A.Speed
FROM   tblSpeeds A
Where  Format(A.TheDate, "YYMMDD") & Cstr(A.RecordID) In 
            (Select TOP 85 PERCENT Format(B.TheDate, "YYMMDD") & Cstr(B.RecordID)
             From   tblSpeeds B
            Where A.TheDate = B.TheDate
            Order by B.Speed DESC)
ORDER BY A.TheDate, A.Speed DESC;

Wayne

 

[WayneRyan]

Preacha,

Easier version ...

SELECT A.TheDate, 
             A.RecordID, 
             A.Speed
FROM   tblSpeeds A
Where  A.RecordID In 
            (Select TOP 85 PERCENT B.RecordID
             From   tblSpeeds B
            Where A.TheDate = B.TheDate
            Order by B.Speed DESC)
ORDER BY A.TheDate, A.Speed DESC;

Wayne

 

[Mike375]

What exactly, is the 85th Percentile of a set of records

 

[WayneRyan]

Mike,

I'm thinking that he means the top 85% of the recordset, by date, sorted by
speed descending.

Wayne

 

[Mike375]

Mike,

I'm thinking that he means the top 85% of the recordset, by date, sorted by
speed descending.

Wayne

As in

100mph
99mh
98mph
.
.
.
.53mph
etc.


as in, only those records that are 85 mph and above are in the game or 123mph if the top is about 144 mph

I am asking the question because I would normally see the the 85th as picking up everything from 85% and down. Must be the Australian accent

Mike

 

[Preacha]

Once again it still doesn't GROUP BY date, atm it shows
24/06/04 ...
24/06/04 55
25/06/04 54/...
25/
26/....

I just want to show
24/06/04 Percentile of that date
25/06/04 Percentile
26/06/04 Percentile

 

[WayneRyan]

Preacha,

Well, Mike and are both confused. The last query gives you the top
85% of the speeds for EACH date.

I think you want each date listed once, and its speed value should be
the speed that beats 85% of the other speeds on that date.

Right?

Wayne