Median of a details section 2

[dgillz]

Normally, I have used median() on a summary value. Now I need to get the median on an array of 7 elements within a single record in the details section.

Here is what I have so far:

WhilePrintingRecords;
Numbervar Array Values:=[{fld1},{fld2},{fld3},{fld4},{fld5},{fld6},{fld 7}];
Sales[(count(Numbervar Array sales))/2];

This returns the middle of the array, or {Fld4}. However, {fld4} is not actually the median, it is just the middle element of my array. To get a mathematical median, what I need to do is sort the elements of the array so that the middle value is always in the middle, so that the last line of my code will actually return a median. Any ideas?

Or if anyone has another way to approach this I am all ears.
Software Training and Support for Macola, Crystal Reports and Goldmine
714-348-0964
dgilsdorf@mchsi.com

 

[synapsevampire]

The best approach is to do this on the database side.

Perhaps you can approach this in Crystal by using a bubble sort:

WhilePrintingRecords;
Numbervar Array Values:=[{fld1},{fld2},{fld3},{fld4},{fld5},{fld6},{fld 7}];
numbervar currentvalue;
numbervar x;
numbervar y;

For x = count(values) to 1 step -1

do(

For y := 2 to x

Do (
currentvalue := values(y-1);
if values(y-1) > values
values(y-1) := values;
values := currentvalue
)
;
)
;

Something like that. I'm not sure if I recall the bubble sort properly, and I didn't test it, but I'm confident you'll work it out.

There are plenty of examples of how to do a bubble sort in other programming languages.

-k

http://www.informeddatadecisions.com

kai@informeddatadecisions.com

 

[Ngolem]

So this is like an examination results thing where you have 7 test results and want the median value

Bubble sort works and it may be ok here but it can be slow...I thought of a different way to sort using the Maximum function

Start with your assigned array

WhilePrintingRecords;
Numbervar Array Values:= [{fld1},{fld2},{fld3},{fld4},{fld5},{fld6},{fld 7}];
Numbervar Array Final := [0,0,0,0,0,0,0];
Numbervar m;
NumberVar n;
NumberVar MedianValue;

//sorting
for m := 1 to ubound(Values) do
(
Final[m] := Maximum(Values);
for n := 1 to ubound(Values) do
(
if Values[n] = Final[m] then
(
Values[n] := 0;
Exit For;
);
);

MedianValue := Values[(count(Numbervar Array Values))/2];

Kind of a weird sort that should work....I haven't tested it though....hope it helps. Jim Broadbent

 

[Ngolem]

Argghhhh...as soon as I pressed submit I saw the bug!

this should be changed in the last line

MedianValue := Final[(count(Final))/2];

Jim Broadbent

 

[synapsevampire]

Jim: Yeah, a Bubble sort is notoriously slow, but I doubt that you'll see a dramatic improvement with yours as it uses another array and the maximum function, with about the same number of iterations.

Consider the quicksort algorithm for better performance, or a shell sort variation of the bubble sort. Both of which were beyond the amount of time I might apply to this, Naith may be interested in pursuing them if performance is poor.

Also, I'd change the return value to:

Final[4], since the Median is always in the 4th position.

-k

http://www.informeddatadecisions.com

kai@informeddatadecisions.com

 

[dgillz]

Good point, when I first wrote the formula it was unknown as to how many elements the array would have, hence my code. Along those same lines what if one of the values is zero or null? Software Training and Support for Macola, Crystal Reports and Goldmine
714-348-0964
dgilsdorf@mchsi.com

 

[synapsevampire]

dgillz: I considered the null/zero issue, but you can adjust the null to zero easily enough prior to running a bubble sort, and in Jim's example you can just adjust his hard coded padding number to something that won't be repeated (-99999999999 perhaps?).

Either of our formulas take into consideration an unlimited number of elements, the only difference being my latest addition of returning array[4] if it's always 7.

I wondered how the (count(Final))/2 was going to work since it will return 3.5...

Might have to do a small tweak there too.

-k

http://www.informeddatadecisions.com

kai@informeddatadecisions.com

 

[Ngolem]

SV...I don't know if my off-the-cuff method is any faster really . It just hit me as a sort of one pass through a small number set method of sorting.

"I wondered how the (count(Final))/2 was going to work since it will return 3.5..."

yes...this is true...now in an odd number of values you would want the value of this calculation rounded up to 4

like this: round((count(Final))/2,0)

I am not sure what the rules are for an even number of values????...for 4 values...is it the 2nd or 3rd value that is the median???

Jim Broadbent

 

[dgillz]

Jim,

I finally got back to this project and I am getting an error on your formula:

The ) is missing.

I cannot figure out where to put this. Any ideas?

Software Training and Support for Macola, Crystal Reports and Goldmine
714-348-0964
dgilsdorf@mchsi.com

 

[Naith]

...
//sorting
for m := 1 to ubound(Values) do
(
Final[m] := Maximum(Values);
for n := 1 to ubound(Values) do
(
if Values[n] = Final[m] then
(
Values[n] := 0;
Exit For;
);
);
);

Naith

 

[dgillz]

Thanks for the help Naith. I am getting a zero returned for the following. Any ideas?

WhilePrintingRecords;
Numbervar Array Values:= [5000,1000,3000,6000,7000,2000,4000];
Numbervar Array Final := [0,0,0,0,0,0,0];
Numbervar m;
NumberVar n;
NumberVar MedianValue;

//sorting
for m := 1 to ubound(Values) do
(
Final[m] := Maximum(Values);
for n := 1 to ubound(Values) do
(
if Values[n] = Final[m] then
(
Values[n] := 0;
Exit For;
);
);
);



MedianValue := Values[(count(Numbervar Array Values))/2]; Software Training and Support for Macola, Crystal Reports and Goldmine
714-348-0964
dgilsdorf@mchsi.com

 

[Naith]

Yeah. Don't count Values. Count Final.

Naith

 

[Ngolem]

I see you are getting good advice while I'm away

As Naith said this line should refer to Final....which is the sorted array


MedianValue := Final[(count(Numbervar Array Final))/2];

But one thing bothers me....In an odd number of array elements you will end up with a fraction in this number so you should probably round up a number to get the 4th array element out of 7...like this

MedianValue := Final[round(count(Numbervar Array Final))/2,0)];

Jim Broadbent

 

[Ngolem]

BTW...I thought of this algorithm off-the-cuff....does it seem to be an efficient sort?? Jim Broadbent

 

[dgillz]

Thanks Jim and Naith, one star apiece for you

Jim, I have this working at home right now, but my little dinky demo database is very small so it is hard to say on performance. I should have this in production by Monday at the latest and I will let you know. Software Training and Support for Macola, Crystal Reports and Goldmine
714-348-0964
dgilsdorf@mchsi.com