Percentile on summarised data

[FrankTWall]

I have searched this forum and cannot get my head around the correct way to solve this problem
CRW V8.5, Oracle DB

I have a report with 3 groups
G1 - Case type
G2 - case status
G3 - case ID

I have a formula that identifies which events in a case I want to count in the details section and I have then summed it in Group 3. Formula is very simple

if (isnull({CSRCSEV.CSRCSEV_DAYS}) or {CSRCSEV.CSRCSEV_DAYS} =1 )then 1

For example,

G1 - Civil
G2 - Active Sum
G3 - case# 1111 2 (2 events for that case meet the criteria etc)
case# 2222 2
case# 3333 3
case# 4444 4
case# 4477 5

G1 - Criminal
G2 - Active
G3 - Case# 5555 4
Case# 6666 7

G1 - Civil
G2 - Pending
G3 - Case# 321 5
Case# 4456 6

G1 - Criminal
G2 - Pending
G3 - Case# 32221 5
Case# 445633 6
Case# 335633 9


What I am trying to get is the 90th percentile of the summarised field for each group i.e Criminal pending cases.

I was hoping to modify the following MEDIAN formula to get the 90th percentile but the brain is not performing:

whileprintingrecords;
numbervar medianappfinal;
numbervar counteraf;

if (GroupName ({CWRCASE.CWRCASE_CTYP_CODE}) = "Appeals"
and GroupName ({CWRCASE.CWRCASE_CASE_STATUS}) = "1.00" )

then

(if
(if (GroupName ({CWRCASE.CWRCASE_CTYP_CODE}) = "Appeals"
and GroupName ({CWRCASE.CWRCASE_CASE_STATUS}) = "1.00" )
then counteraf:= counteraf + 1
else
counteraf:= counteraf)
= round(DistinctCount ({CWRCASE.CWRCASE_CASE_ID}, {CWRCASE.CWRCASE_CASE_STATUS})/2,0)
then medianappfinal := Sum ({@Event flag - all}, {CWRCASE.CWRCASE_CASE_ID})
else medianappfinal := medianappfinal

)
else
medianappfinal;

(I then have a display formula)

Any assistance would be greatly appreciated
Frank

 

[Madawc]

You might have better luck using Running Totals. They have a lot of the functions built-in.

Madawc Williams (East Anglia, UK)

 

[lbass]

First do a topN sort on sum of your conditional formula, which we will call {@cond}. Then create three formulas:

//{@reset} to be placed in GH#1:
whileprintingrecords;
numbervar sumamt := 0;
numbervar no90 := 0;
numbervar percentile90 := 0;
numbervar ave1 := 0;
numbervar ave2 := 0;
numbervar grpcnt := 0;

//{@calc} to be placed in the group header or footer for group#3:
whileprintingrecords;
numbervar sumamt := sum({@cond},{table.caseID});
numbervar no90 := .9 * distinctcount({table.caseID},{table.casetype});
numbervar percentile90;
numbervar ave1;
numbervar ave2;
numbervar grpcnt := grpcnt + 1;

if int(no90) <> no90 then
(if grpcnt = round(no90) then
percentile90 := sumamt) else
if int(no90) = no90 then
(
if grpcnt = no90 then
ave1 := sumamt;
if grpcnt = no90 + 1 then
ave2 := sumamt;
percentile90 := (ave1 + ave2)/2
);

{@displ} to be placed in GF#1:
whileprintingrecords;
numbervar percentile90;

-LB

 

[FrankTWall]

Many thanks LB- got it working
Frank