Exclude top 20%

[mcquam]

I am preparing a summary report that shows the number of days that it takes to complete each case for each month. A service level exists that allows the worst 20% of cases to be excluded from the average days for each month. I have listed each case duration in the details and grouped by month. I have calculated the average durations for each month using a summary of the days per case.
I now need to select the longest 20% of durations for the month and deduct them to calculate the new average for each month. I tried a running total but that didn't work.

My report looks like this:

completed cases ave days
Client1 549 30.49 (Group1)
Month1 32 22.47 (Group2)
Case1 running tot (Detail)
Case2 etc.
I am using CR9 and an access database.

 

[lbass]

We need to see the calculation for number of days to complete a case. Be sure to show the content of any nested formulas, too.

-LB

 

[mcquam]

The field for number of days for each case is not calculated. I have summarised it to give an average for client (Group1) and month (Group2). I calculated the 20% like this:

round (Count ({FTRK_Reports.CompletionDate}, {FTRK_Reports.CompletionDate}, "monthly") * 0.2)

where {FTRK_Reports.CompletionDate} is the number of days field. My problem is how to calculate a new average number of days after deducting only the worst numbers.

 

[mcquam]

I meant to say the number of days field is a count of {FTRK_Reports.CompletionDate}.

 

[mcquam]

Sorry, I spoke too soon. It is actually the difference between 2 date fields:

{FTRK_Reports.CompletionDate}-{FTRK_Reports.OfferRcvd} and the group is counted.

 

[lbass]

First sort by the datediff formula in descending order. Then create three formulas:

//{@reset} for the group header:
whileprintingrecords;
numbervar cnt := 0;
numbervar cntx := 0;
numbervar sumdiff := 0;

//{@accum} to be placed in the detail section:
whileprintingrecords;
numbervar cnt := cnt + 1;
numbervar cntx;
numbervar sumdiff;

if cnt > round(.2* Count ({FTRK_Reports.CompletionDate}, {FTRK_Reports.CompletionDate}, "monthly")) then (
cntx := cntx + 1;
sumdiff := sumdiff + datediff("d",{FTRK_Reports.OfferRcvd},{FTRK_Reports.CompletionDate})
);

//{@ave} to be placed in the group footer:
whileprintingrecords;
numbervar cntx;
numbervar sumdiff;
sumdiff/cntx;

-LB

 

[mcquam]

Worked perfectly straight away. Stunning! I've been working on that for nearly a week and now realise I would never have got there. Many thanks.

 

[mcquam]

I now realise this has worked for the first month only. Looks like I need to reset it for each month. Can you help?

 

[mcquam]

OK I put it in the wrong header. Thanks