90th Percentile excluding null values 1

[shelby55]

Hi

I'm using Crystal 8.5 reporting from an SQL database. The database is hospital emergency department visits.

I have a report that is summarized on visit date showing various data elements i.e. total number of visits, total number of patient left without being seen etc.

Another required metric is ER length of stay at the 90th percentile. I know how to do this but the issue is that I want it to only calculate for cases where there is a valid length of stay. Because this report has all cases, it may not include the case in the LOS calculation but the zero value will be included in the 90th percentile.

So what I've had to do is create a subreport with only valid ER LOS cases and calculate the 90th percentile in that and link to the main report.

BUT is there any way I could do this in the main report because it is causing issues with speed of the report because I have 3 such subreports for 90th percentile.

Thanks.

 

[lbass]

This seems to work correctly. Create a formula {@null} by opening and closing a new formula without entering anything. Then create a formula for your field {@LOS} that contains zeros:

//{@LOSnozeros}:
If {@LOS} = 0 then
tonumber({@null}) else
{@LOS}

Then create a formula {@pth90}:

pthpercentile(90,{@LOSnozeros},{table.groupfield})

-LB

 

[shelby55]

Hi LB

Sorry for the late thanks but I only got to try this out today....and it works!! You are a genius - thanks so much.