Help with Getting median

[geestrong]

Hello,

I am trying to get the Median based on certain criteria. I found these formulas on Business Objects website, and I am trying to modify them work for the report. I am using CR XI. Oracle DB.


Here are the formulas:

Note: the @BackLogResEval is a running total counting the records that fit the criteria. I know that I have 16 records that fit this criteria. Also I have a display formula names @BackLogResDisplay that display the number.

//Evaluate formula - placed in the detail section
WhilePrintingRecords;
EvaluateAfter ({@BackLogResEval});
NumberVar MedianRecNo;
NumberVar OddNoOfRecFlag;
NumberVar Median;

If (OnFirstRecord or {RPVW_PERMIT_SUMMARY.PERMIT_NUMBER} <> Previous({RPVW_PERMIT_SUMMARY.PERMIT_NUMBER}))
and {RPVW_PERMIT_SUMMARY.PERMIT_TYPE} in ['BLR1', 'BLR2' , 'BLR3' ,'BLRS' ,'BLR4', 'B-D1']
and {@CompletedPlans} = 'NO'
then

if {@BackLogResEval} = MedianRecNo then
(
if OddNoOfRecFlag = 0 then

Median := {@BackLogResEval}
else
Median := ({@BackLogResEval} +{@BackLogResEval}) / 2
)

Note: I tried to use a Next or Previous function but I get an error saying "NO Previous Or Next value.' So how can I step though each record?


This is in my initializing formula placed in the Report footer
//Initializing formula
WhilePrintingRecords;
EvaluateAfter ({@BackLogResDisplay});
NumberVar MedianRecNo;
NumberVar OddNoOfRecFlag := 1;
MedianRecNo := Round (({@BackLogResDisplay}) / 2);
if Remainder(({@BackLogResDisplay}), 2) > 0 then
OddNoOfRecFlag := 0



Thanks,
Greg

 

[Madawc]

You could try it with Running Totals, rather than formulas. This includes the Median function and evaluation by formula.

Right-click on a field and choose Insert to get a choice of Running Total or Summary. Or else use the Field Explorer, the icon that is a grid-like box, to add running totals.

Madawc Williams (East Anglia, UK)

 

[geestrong]

Thanks for the respones to my question, but that is this the problem... I can not do a summary on that formula, so I am trying us a manual running total

 

[lbass]

What is the content of {@Backlogreseval}? How is the running total contained it created? It would help to have a sample report display. Could you use a conditional formula instead of the running total within {@Bakclogreseval} or do you have record inflation in your report?

-LB

 

[geestrong]

Hello lbass,

The BackLogResEval is subtracting dates. I am subtacting the earliest date that document that come to the department and subtracting that from the enddate that the user enters. here is the formula:
// Residential
WhilePrintingRecords;
NumberVar DaysForRes;

if(OnFirstRecord or {RPVW_PERMIT_SUMMARY.PERMIT_NUMBER} <> Previous({RPVW_PERMIT_SUMMARY.PERMIT_NUMBER}))
and ({RPVW_PERMIT_SUMMARY.PERMIT_TYPE} in ['BLR1', 'BLR2' , 'BLR3' ,'BLRS' ,'BLR4', 'B-D1'])
and ({@CompletedPlans}='NO')
then
if isnull(GroupName ({PER_ACTIVITY.PER_ACT_DECISION}))
then
DaysForRes := {?EndDate}-{PER_ACTION.PER_ACN_COMPL_DATE}
else
if (Maximum ({PER_ACTIVITY.PER_ACT_COMPL_DATE}, {RPVW_PERMIT_SUMMARY.PERMIT_NUMBER}) <= {?EndDate})
then
DaysForRes := {?EndDate} - Maximum ({PER_ACTIVITY.PER_ACT_COMPL_DATE}, {RPVW_PERMIT_SUMMARY.PERMIT_NUMBER})
else
if
(Maximum ({PER_ACTIVITY.PER_ACT_COMPL_DATE}, {RPVW_PERMIT_SUMMARY.PERMIT_NUMBER})>= {?EndDate})
then
DaysForRes := Maximum ({PER_ACTIVITY.PER_ACT_COMPL_DATE}, {RPVW_PERMIT_SUMMARY.PERMIT_NUMBER}) - {?EndDate}

There are only an few docs that have not been complete. I am only trying to find the median for those few days.

 

[lbass]

Your formulas are complex, so it's hard to tell exactly what to recommend, but I think one problem is your attempt to initialize the variables in the report footer. Even using the {@evaluateafter}, I don't think that will work, since the report footer is the last section to be evaluated. Also note that you refer to the earliest date but are using a maximum, which would give you the most recent date.

If you don't have record inflation, this could all be simplified by using conditional formulas instead of running totals.

If the running totals are necessary, then I would suggest creating a subreport in the report header (by saving your main report under a different name and importing it as a subreport) that counts the records meeting your criteria. So in the subreport, you would have to add this formula:

// {@CntResidential}:
WhilePrintingRecords;
Shared NumberVar CntRes;

if(OnFirstRecord or {RPVW_PERMIT_SUMMARY.PERMIT_NUMBER} <> Previous({RPVW_PERMIT_SUMMARY.PERMIT_NUMBER}))
and ({RPVW_PERMIT_SUMMARY.PERMIT_TYPE} in ['BLR1', 'BLR2' , 'BLR3' ,'BLRS' ,'BLR4', 'B-D1']) and ({@CompletedPlans}='NO') then
CntRes := CntRes + 1;

Then in your main report, you would eliminate the initialization formula, still use {@BackLogResEval}, and then create another formula something like:

whileprintingrecords;
shared numbervar CntRes;
numbervar RtCnt;
NumberVar Medianx;
Numbervar ave1;
numbervar ave2;

If (OnFirstRecord or {RPVW_PERMIT_SUMMARY.PERMIT_NUMBER} <> Previous({RPVW_PERMIT_SUMMARY.PERMIT_NUMBER}))
and {RPVW_PERMIT_SUMMARY.PERMIT_TYPE} in ['BLR1', 'BLR2' , 'BLR3' ,'BLRS' ,'BLR4', 'B-D1']
and {@CompletedPlans} = 'NO' then
RtCnt := RtCnt + 1;
if remainder(CntRes,2) <> 0 then
(if RtCnt = round(CntRes/2,0) then
medianx := {@BackLogResEval});
if remainder(CntRes,2) = 0 then
(if RtCnt = CntRes/2 then ave1 := {@BackLogResEval};
if RtCnt = CntRes/2 + 1 then ave2 := {@BackLogResEval};
medianx := (ave1 + ave2)/2);
medianx;

-LB

 

[geestrong]

I will give this a try tomorrow... thanks for you helpl

 

[geestrong]

This did not work for me. I tried to use an array but could not get it to work. any other suggestion?

 

[lbass]

You need to explain in what you mean by "this did not work for me", so we can work from there. Maybe provide some sample data of the results you got using my suggestion.

-LB

 

[geestrong]

Sorry for being so vague.

Here is a sample of the data. Note this is a running total
this formula is what is giving me the difference between days:
//@DaysResEval
WhilePrintingRecords;
NumberVar DaysForRes;

if(OnFirstRecord or {RPVW_PERMIT_SUMMARY.PERMIT_NUMBER} <> Previous({RPVW_PERMIT_SUMMARY.PERMIT_NUMBER}))
and ({RPVW_PERMIT_SUMMARY.PERMIT_TYPE} in ['BLR1', 'BLR2' , 'BLR3' ,'BLRS' ,'BLR4', 'B-D1'])
and ({@CompletedPlans}='NO')
then
if isnull(GroupName ({PER_ACTIVITY.PER_ACT_DECISION}))
then
DaysForRes := {?EndDate}-{PER_ACTION.PER_ACN_COMPL_DATE}
else
if (Maximum ({PER_ACTIVITY.PER_ACT_COMPL_DATE}, {RPVW_PERMIT_SUMMARY.PERMIT_NUMBER}) <= {?EndDate})
then
DaysForRes := {?EndDate} - Maximum ({PER_ACTIVITY.PER_ACT_COMPL_DATE}, {RPVW_PERMIT_SUMMARY.PERMIT_NUMBER})
else
if
(Maximum ({PER_ACTIVITY.PER_ACT_COMPL_DATE}, {RPVW_PERMIT_SUMMARY.PERMIT_NUMBER})>= {?EndDate})
then
DaysForRes := Maximum ({PER_ACTIVITY.PER_ACT_COMPL_DATE}, {RPVW_PERMIT_SUMMARY.PERMIT_NUMBER}) - {?EndDate}

I am hiding this information from the users.
so if the end date is 5/31/2005 and this permit went to the agency on 5/01/31 it would have been in that agency for 31 days. Also, I have another formula that checks if the permit is on hold and I am just checking for 'HOLD' or NULL' and that is called @CompletedPlans and that returns either a "YES" or "NO".


I have created a subreport that only contains 'NO' values. Next I need to find the MEDIAN of all the permits that are now.

sample data

PermNum daysinbacklog
1 30
2 45
3 6
4 2
5 4
6 5
etc....
for the example above the Median should be 4 days.

I have a count of the number that are in backlog and that formula is called @BackLogResEval (which is a running total) and that is display with the @BackLogResDisplay.

I Used your formulas and I got 0 for median.

 

[synapsevampire]

Are you certain that you want the median? I think that the example provided states otherwise.

Here's a definition I pulled from a site that might help you to understand:

'Definition: "Middle value" of a list. The smallest number such that
'at least half the numbers in the list are no greater than it. If the
'list has an odd number of entries, the median is the middle entry in
'the list after sorting the list into increasing order. If the list
'has an even number of entries, the median is equal to the sum of the
'two middle (after sorting) numbers divided by two.

As you can see, 4 would not be the median value for your example data, I think that the result would be 5.5

So I won't bother answering this until you can clearly identify what it is that you want, however it is fairly simple to work out a median given your requirements, but since your example shows that you don't want the median, I need clarification.

-k

 

[lbass]

Is the subreport for the "no's" new? First I've heard of it. It's unclear now whether you are trying to get the median in the subreport or in the main report, and if in the main report, what the relevance of the subreport is.

The important point of my earlier suggestion is that you must have the value of the running total variable "CntRes" before the beginning of the report, so that you are able to use it to identify the record that is the median. My suggestion wouldn't work unless you followed all steps exactly.

-LB

 

[geestrong]

Thanks, Synapsavampire

clarification... I must have been thinking about the mean.. Yes I am looking for the median. You are correct! Thanks for the correction.

 

[geestrong]

LB
I am trying to get the median for the subreport for the 'NO's. Ok as for the running total variable "CntRes", I already have a formula that counts the "NO"s... I can replace the variables for clarity. the variable is named "BackLogResCount".

thanks
Greg

 

[lbass]

If this is your subreport data, please explain whether these are details or group summaries:

PermNum daysinbacklog
1 30
2 45
3 6
4 2
5 4
6 5

-LB

 

[geestrong]

LB,

It is in the detail section of the subreport.

Greg

 

[lbass]

That makes it easy. First you would need to sort by {table.daysinbacklog}. Then you would do something like:

whileprintingrecords;
numbervar medianx;
numbervar ave1;
numbervar ave2;

if remainder(count({table.permnumber})/2,2) <> 0 then
if recordnumber = round(count({table.permnumber})/2) then
medianx := {table.daysinbacklog};
if remainder(count({table.permnumber})/2,2) = 0 then
(if recordnumber = count({table.permnumber})/2 then
ave1 := {table.daysinbacklog};
if recordnumber = count({table.permnumber})/2 + 1 then
ave2 := {table.daysinbacklog};
medianx := (ave1 + ave2)/2);
medianx;

In your recent example, with the data sorted, you would see:

PermNum daysinbacklog
4 2
5 4
6 5
3 6
1 30
2 45

Because there are an even number of groups, my formula would give you a median daysinbacklog that was an 5.5--averaging the backlog figures (5 and 6) for permnums 6 and 3. If there were another row, as in:

PermNum daysinbacklog
4 2
5 4
6 5
3 6
1 30
2 45
7 53

...the result would be 6.

-LB

 

[geestrong]

LB,

The daysinbacklog is from a formula it is not a database field. Days In Back Log comes from a formula called @DaysResEval and I can not sort it!

Greg

 

[lbass]

And the content of the formula is?

-LB

 

[geestrong]

Here are the contents of the formula:
This formula is in the detail section

//@DaysForResEval
WhilePrintingRecords;
NumberVar DaysForRes;

if(OnFirstRecord or {RPVW_PERMIT_SUMMARY.PERMIT_NUMBER} <> Previous({RPVW_PERMIT_SUMMARY.PERMIT_NUMBER}))
and ({RPVW_PERMIT_SUMMARY.PERMIT_TYPE} in ['BLR1', 'BLR2', 'BLR3' ,'BLRS' ,'BLR4', 'B-D1'])
and ({@CompletedPlans}='NO')
then
if isnull(GroupName ({PER_ACTIVITY.PER_ACT_DECISION}))
then
DaysForRes := {?EndDate}-{PER_ACTION.PER_ACN_COMPL_DATE}
else
if (Maximum ({PER_ACTIVITY.PER_ACT_COMPL_DATE}, {RPVW_PERMIT_SUMMARY.PERMIT_NUMBER}) <= {?EndDate})
then
DaysForRes := {?EndDate} - Maximum ({PER_ACTIVITY.PER_ACT_COMPL_DATE}, {RPVW_PERMIT_SUMMARY.PERMIT_NUMBER})
else
if
(Maximum ({PER_ACTIVITY.PER_ACT_COMPL_DATE}, {RPVW_PERMIT_SUMMARY.PERMIT_NUMBER})>= {?EndDate})
then
DaysForRes := Maximum ({PER_ACTIVITY.PER_ACT_COMPL_DATE}, {RPVW_PERMIT_SUMMARY.PERMIT_NUMBER}) - {?EndDate}

Greg