help with a report 1

[nathanharcup]

i am being asked for a report to show how long it took to solve 90% of jobs.

exampleofdata

jobNo Business Days
3 0.38
4 0.5
2 1.58
7 1.7
1 1.71
5 2.3
9 5.9
10 6.1
6 6.7
8 10.3

average= 2.99


I need to be able to take off the top 10% and work out the average of the 90% is left. The top 10% being the business hours /days.

So job number 8 took the longest and out of the 10 jobs that is the top 10%. So if we remove that and work out the average of the rest the result would be 2.99 Days

 

[nathanharcup]

I have been able to work out the average days for all 10 jobs, i just dont understand how i get the 90% only

thanks

 

[lbass]

What version of CR are you using (always specify this)? Is the data you want to work with (as shown above) in the detail section or are these summaries per job?

-LB

 

[nathanharcup]

sorry it's crystal 11r2.

The data is in the details section as per the above example, i then have the data grouped by type with average days summary in the group footer.

Thanks

 

[lbass]

Try inserting a running total that does an average of business days, evaluate using a formula:

{table.businessdays} < pthpercentile(90,{table.businessdays},{table.type})

Reset on change of group: {table.type}.

Not sure whether you want that to be <= (to include the value at 90%).

-LB

 

[nathanharcup]

Hi LB,
Thanks very much for this it works exactly as I want, but was wondering if you know of a way to pass in the 90 from a parameter field ? it fails with the message that it must be a number.
The parameter is set up as a number. I have also tried creating a formula that sets the parameter to a number i.e.

tonumber({?param})

to no avail

Thanks

 

[lbass]

PthPercentile will only accept a constant, so try the following which appears to match the PthPercentile value. This assumes you are sorting ascending by {table.busdays}.

whileprintingrecords;
numbervar cnt := cnt + 1;
numbervar st;
numbervar end;
numbervar pct;
numbervar sumx;
numbervar cntx;
if cnt >=
{?percentile}/100*count({table.busdays},{table.type})+.5 then (
st := previous({table.busdays});
end := {table.busdays};
cnt := 0;
);
pct := (st+end)/2;
if {table.busdays} < pct then (
sumx := sumx + {table.busdays}
cntx := cntx + 1
);
pct //to display the selected percentile

Add a reset formula to the type group header:
whileprintingrecords;
numbervar cnt := 0;
numbervar st := 0;
numbervar end := 0;
numbervar pct := 0;
numbervar sumx := 0;
numbervar cntx := 0;

Display the average in the group footer:
whileprintingrecords;
numbervar sumx;
numbervar cntx;
sumx/cntx

-LB