Calculate 90th Percentile

[shelby55]

Hi

I searched on "percentile" which resulted in 3 threads. One post didn't apply and 2 had responses saying "there are plenty of percentile threads" and advising to search but not indicating links to the threads or advising how to do it.

So could someone please advise how I calculate percentile or point me to the threads where this is discussed?

Specifically I am using Access 2003. I have a table with patient data at the visit level. What I would like to do is calculate the 90th percentile per day, week and month of the patient's length of stay.

All assistance greatly appreciated.

 

[PHV]

You may find the following interesting for you:

http://www.mvps.org/access/queries/qry0019.htm

http://www.fabalou.com/Access/Modules/percentile.asp

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

 

[shelby55]

Hi PHV

Thanks very much. Can you give me any other advice i.e. I'm not very fluent in VBA.

So do I create a query and do this? How do I apply the code, where do I place it and how do I call it?

Thanks very much.

 

[PHV]

I'm not very fluent in VBA
So, here a pure SQL starting point:

SELECT ...
, (SELECT Min([i]FieldName[/i]) FROM [i]TableName[/i] A
   WHERE (SELECT Count(*) FROM [i]TableName[/i] B WHERE B.[i]FieldName[/i]<=A.[i]FieldName[/i])
    >= 90 * (SELECT Count(*) FROM [i]TableName[/i])) AS [90th Percentile]
FROM ...

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

 

[shelby55]

Hi PHV

Sorry, just getting back to this.

I'm not understanding fully your comments because I'm still confused about where the aggregration comes in.

For instance, I have a table with patient visit level data. I have unique visit ID, visit date, triage level, length of stay (LOS) and denominator. The denominator column is because not all cases apply to the LOS so I want to be able to sum LOS and divide by sum of the denom if I were to calculate average.

Using the above, how would I find the 90th percentile per visit date? Thanks.

 

[SkipVought]

Hi,

Is your data in Access or Excel? Seemed as if you were designing an application in Excel. But maybe that was something else.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[shelby55]

Hi Skip

Thanks for checking in. I'm actually working now on the data going INTO the Excel document you've been so wonderful in helping me with!!

As you know, the Excel workbook has a "raw data" page which is populated by a query and that data comes from Access.

So this request is for an Access database - do you work with Access too?

 

[SkipVought]

So where are you coding the query of the data that will end up in Excel?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[shelby55]

Hi

Some background and clarification on the process:
The Excel workbook is a reporting tool that has to be automatically updated daily with the previous days' data but as you've seen it also has 1 years' worth of data in it for users to select and view other timeframes.

The Excel workbook also has a page of "raw data" that feeds the graphs/indicators within other worksheets within the same workbook.

The original data is from hospital ADT (admission, discharge and transfer) software and is in text format.
It made more sense for automation but also
for data quality/validation as well as control to approach it as two tiered so that the data is imported into Access at visit level (one line of data per visit) and a query built grouped on date of visit that would populate the raw data worksheet. One of the fields is length of stay at the 90th percentile thus my thread.

So bottom line: the query will be in Access.

Thanks Skip!!

 

[SkipVought]

So how are you getting the query result data from Access to Excel?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[shelby55]

Hi

Right now there is a query but it doesn't currently have 90th percentile. I used MSQuery initially and Refresh All so the data is automatically refreshed with the new results from Access when the Excel workbook is opened.

 

[SkipVought]

Okay, then PHV gave you a solution that you can use in your MS Access query to calculate the 90 percentile.

Why will that not work?

Or you could use some aggregation formulas in Excel to calculate percentiles on the sheet.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[shelby55]

Hi Skip

The problem is I don't know what fields PHV is using for the min/max which is why I provided my table structure/data elements to see if he/you/any other kind soul could determine what field goes where in his formula.

Plus I'm not sure why he has two different tables. Was there an initial step to create one of these tables? Where does the aggregration come in for the visit date versus week versus month?

I would prefer not to use aggregration formulae within Excel because that would be another worksheet of data to save with the copy so the graphs/indicators will all have the source data to run and the connection to Access can be cut.

Thanks.

 

[PHV]

why he has two different tables
???
Same table used 2 times with different alias.

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

 

[SkipVought]

What is your arithmetic for calculating percentile, using your data fields?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[shelby55]

Thanks very much.

So AFieldName and BFieldName are the same fields? I should have stated I'm also not well versed in SQL......

 

[SkipVought]

[tt]A.FieldName and B.FieldName are the same fields[/tt]

But lets start with your percentile calculation, without regard to SQL.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[shelby55]

Hi Skip

It seems we both posted at the same time and I didn't see yours asking "What is your arithmetic for calculating percentile, using your data fields?" I don't have any calculation right now because I've never used Access to calculate this. Most of my reporting/analysis is with Crystal Reports which has this function built in.

 

[SkipVought]

I don't have any calculation right now because I've never used Access to calculate this.

Knowing Access has ABSLOUTELY NOTHING to do with YOUR arithmetic.

YOU and no one else, YOU need to know what calculation YOU need. NO VBA, NO SQL, just YOUR arithmetic expression, that represents YOUR calculation of percentile. THIS is your basic requirement that we will translate in to SQL or VBA or sheet formulas, whichever might work the best for your circumstance.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[shelby55]

Hi Skip

You lost me. I don't create the calculation of percentile, it's a function with an algorithm that I don't know thus this thread.

When I say 90th percentile I describe it as 9/10 patients on this day, this week, or this month who were treated within n number of hours.

What is it that you're wanting me to answer?