Function to Calculate Percentile for Previous Timeframe

[shelby55]

Hello

I am using Access 2003.

I import data from the hospital ADT software (.txt format) into Access. Each line of data is a single hospital visit. Within Access I create a summary query by visit date with various metrics i.e. total visits, ED LOS (length of stay) etc. The summary query is imported via Excel into a worksheet called RawData and this worksheet is the source of data for various tables and graphs within the workbook.

One such worksheet is “summary” which displays various metrics per a visit date selected by the user (via pull down list). A metric on the summary is 90th percentile ED LOS which PHV provided a function for in previous post. Another metric is the 90th percentile ED LOS for the last 7 days and then also 30 days. So this isn’t like where I can select a data element from the date of 7 days ago, this will be a calculation of the 90th percentile for the last 7 or 30 days combined.

So if the date selected is April 10 then the 7 day calculation needs to be what the ED LOS 90th percentile was for data from April 3 to 9. In order to calculate display, I would also need this data element to show up in the summary on the date in question i.e. for the summary line for April 10, I would need to have the 7 and 30 day 90th percentile ED LOS on the same line in the summary. For the other metrics for 7 and 30 days prior, I can use Match and DSUM for the calculations of 7 days prior but I can’t calculate 90th percentile from 90th percentiles.

Below is the function created by PHV to calculate 90th percentile for the specified:

Option Compare Database
'code provided by PHV from TekTips

Public Function XthPercentile(SQL As String, ColName As String, X As Double) As Variant
Dim Percentile As Double
Dim XthRec As Double
Dim iRec As Long
Dim rst As DAO.Recordset
Set rst = CurrentDb.OpenRecordset(SQL, dbOpenDynaset)
With rst
  If Not (.BOF Or .EOF) Then
    .MoveLast
    If X = 1 Then Percentile = .Fields(ColName)
    .MoveFirst
    If X = 0 Then Percentile = .Fields(ColName)
    If X > 0 And X < 1 Then
      XthRec = 1 + X * (.RecordCount - 1)
      iRec = Int(XthRec)
      .Move iRec - 1
      Percentile = .Fields(ColName)
      XthRec = XthRec - iRec
      If XthRec > 0 Then
        .MoveNext
        Percentile = Percentile + XthRec * (.Fields(ColName) - Percentile)
      End If
    End If
    XthPercentile = Percentile
  End If
  .Close
End With
Set rst = Nothing
End Function

So how can I calculate 90th percentile for 7 and 30 days prior to the current date? Thanks very much.

 

[PHV]

how can I calculate 90th percentile for 7 and 30 days prior to the current date
Call the function with the appropriate SQL.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[shelby55]

Hi

I figured it out. Using PHV's brilliant function, I can select the date of 7 days ago by using dateadd function and if I want the cumulative last 7 days then the date in the function is between -1 and -6 days ago.

Thanks.