Median - on summarised data 1

[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 status
G2 - case type
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 - Finalised
G2 - Criminal 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

G2 - Civil
G3 - Case# 5555 4
Case# 6666 7

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

What I am trying to get is a median of the summarised field for each group
As per lbass's suggestion on other threads re this issue I have done a Top/N sort by the summary field and then placed a variable formula in Group 3 header

whileprintingrecords;
numbervar medianx;
if groupnumber = round(DistinctCount ({CWRCASE.CWRCASE_CASE_ID}, {CWRCASE.CWRCASE_CTYP_CODE})/2,0)
then
medianx := Sum ({@Event flag - all}, {CWRCASE.CWRCASE_CASE_ID}) else medianx := medianx;

I then have a display formula:
whileprintingrecords;
numbervar medianx;
in the Group report footer.

I am getting the correct return from the variable formula in Group 3 - for the first group (I know I have not allowed for even/uneven number of groups) BUT I cannot reset it to calculate for different groups
I have also replicated it to try and select only a certain type of case but this has failed
e.g.
whileprintingrecords;
numbervar medianx;
if
{CWRCASE.CWRCASE_CASE_ID} startswith "AP" and

groupnumber = round(DistinctCount ({CWRCASE.CWRCASE_CASE_ID}, {CWRCASE.CWRCASE_CTYP_CODE})/2,0)
then
medianx := Sum ({@Event flag - all}, {CWRCASE.CWRCASE_CASE_ID}) else medianx := medianx;

Anyone with any ideas?
Frank

 

[lbass]

Instead of using groupnumber, add a counting variable, as in:

whileprintingrecords;
numbervar medianx;
numbervar counter := counter + 1;
if counter = round(DistinctCount ({CWRCASE.CWRCASE_CASE_ID}, {CWRCASE.CWRCASE_CTYP_CODE})/2,0)
then
medianx := Sum ({@Event flag - all}, {CWRCASE.CWRCASE_CASE_ID}) else medianx := medianx;

Also add a reset formula in the group #2 header:
whileprintingrecords;
numbervar medianx := 0;
numbervar counter := 0;

-LB

 

[FrankTWall]

Ibass you are a legend and many thanks for the quick reply
Now(there is always a follow up question), what is the best way to display the median for EACH group (they have to either be in the GF or RF

Many thanks again

 

[lbass]

You should be using the following formula in the group #2 footer:

whileprintingrecords;
numbervar medianx;

Or are you saying you want to display a summary of the results at a higher level, e.g., group #1?

-LB

 

[FrankTWall]

Thanks again LB
I want to display the results of the formula for each Group in the Group Footer or report footer depending on where I am displaying other data (from running totals)
I am at another place today but late yesterday I set up a seperate calculation and display formula (slightly modified from your suggestion above)for each group. This seemed to work ok but meant that I had to replicate it (with a change in the selection for each) eight times.
Just thinking I should be able to do it all in one formula and then have seperate display formulas. Will try this tomorrow,
Comments?
Thanks again
Frank

 

[lbass]

I think you're on the right track. You'd have to carry forward each groups median with a variable. Maybe a simpler alternative would be to save the report as a subreport, suppress all but the group footers, and insert it as an unlinked subreport in the report footer.

-LB