90th Percentile

[shelby55]

Hi

I am using Crystal 9.0 reporting from an Access 2002 database.

I have a field of Avg_ERLOS which is based on ERLOS/ERCases. I want to be able to report on the 90th percentile average ER LOS and include the data field on a line graph with total average ER LOS.

How do I do this? Thanks.

 

[lbass]

shelby55,

Your post isn't clear. Is Avg_ERLOS a formula? What is its actual content? Something like the following?

Sum({table.ERLOS}{table.group})/Count({table.ERCases},{table.group})

What are you grouping on?

What would the TOTAL average ER LOS be? How is it calculated?

-LB

 

[shelby55]

Hi LB

Sorry I wasn't clear.

ERLOS is the ER length of stay for patients. However, not all can be included in the formula because some don't have times to include in the datediff formula so must be excluded from the numerator and the denominator.

The formula for ERLOS =
if {@AdmDateCalc}>Date(2000,04,01) and
{@DispDateCalc}>Date(2000,04,01) then
DateDiff("n",{@AdmDateCalc},{@DispDateCalc})/60

The reason for the >Date portion is because even when the date/time is blank, it shows as 1970/01/01 which is due to the database that is downloaded into Access.

So if I were to summarize this field in a group for average ELOS it would be weighted average with the denominator cases field.

What I want to be able to show is what is the 90th percentile avg ELOS. Thanks.

 

[lbass]

You didn't answer all of my questions.

Also, please show some sample data and also clarify how the weighted average is constructed.

-LB

 

[shelby55]

Hi

Thanks for sticking with this LB - sorry I haven't been providing the information you need.

ERLOS formula =
if {@AdmDateCalc}>Date(2000,04,01) and
{@DispDateCalc}>Date(2000,04,01) then
DateDiff("n",{@AdmDateCalc},{@DispDateCalc})/60

ER_denom field =
if {@AdmDateCalc}>Date(2000,04,01) and
{@DispDateCalc}>Date(2004,04,01) then
+1 else 0

Avg ER LOS=
weightedaverage of ERLOS/ER_denom

Total ERLOS=
Sum(ERLOS)

I'd like to use the 90th percentile in a graph which would group on admission month or admission quarter.

I guess what I need to get my head around is that the 90th percentile would be based on each abstract's ER LOS, correct?

The data would look like:
chart admit date/time disch date/time ER LOS
1234 feb 1 2010 11:00 feb 1 2010 12:00 1.0
4567 jan 12 2010 13:00 jan 12 2010 15:00 2.0
8900 jan 5 2010 15:00 jan 5 2010 20:00 5.0

Does this help? Thanks.

 

[lbass]

It seems to me your formula for avg ERLOS would return a division by zero error and I'm not getting how you are setting up the weighted average. As for the percentile, I'm not sure whether you want to evaluate a group summary against the 90th percentile criterion or whether you want to evaluate the a ERLOS at the detail level against the 90th percentile. There is a pthpercentile function, but I believe it is limited to evaluating non-summary data.

While you could set up a variable to determine a summary value at the 90th percentile, you would have to go through hoops to graph it. You could consider using SQL expresssions to return your summaries so that they could be evaluated using the pthpercentile function, but I'm not sure how you'd set up a SQL expression to do a weighted average--if that's what would be entailed.

-LB

 

[shelby55]

Hi LB

Actually my avgERLOS formula is:
if ER_denom>0 then weightedaverage of ERLOS/ER_denom

so it doesn't return a division of zero.

I'm not sure what you're asking related to weighted average.
If I were to include this in a group summary then I would select ERLOS from the details, weighted average of ER_denom to get average by whatever group.

I think that I do want to show 90th percentile on the non-summary data because that is what I'm trying to determine: what is the 90th percentile ER LOS - in other words 90% of all cases are equal to or less than N ERLOS and 10% are greater than that.

So you don't think this is possible? Thanks.

 

[lbass]

In the future please show your actual formulas instead of abstract representations of them.

Then I think you can use the pthpercentile function to identify the 90th percentile:

pthpercentile(90,{@ERLOS},{table.group})

-LB