Median and percentile of sums or average in Crystal 1

[Karchan]

Does anyone know how to compute the median and 25th percentile of group sums (or average) without doing TopN sort?
My report shows sums for each week (custom grouped Monday-Sunday)
08/29/2011-09/04/2011 100
09/05/2011-09/11/2011 80
09/12/2011-09/18/2011 94
....

I don't want to change the display as I need to keep group in ascending week order so I can't do a TopN. How can I calculate the median of the sums and also the 25th percentile?

 

[Madawc]

Try a running total, or possibly a summary total. If you're not already familiar with Crystal's automated totals, see FAQ767-6524.

Madawc Williams (East Anglia, UK). Using Crystal 2008 with SQL and Windows XP

 

[Karchan]

The problem is you can't do a running total on a group sum, as far as I know.

 

[lbass]

You could collect the values in an array, sort the array, and then determine the median and 25th percentile of the assorted array. Thread767-1553861 would help you sort the array. You would need to first set the group summary equal to an array variable, and then try the sorting. Then apply the median calculation to the array.

-LB

 

[Karchan]

Thanks LB, I tried the formula you posted in the thread and it seems to work if I convert my group sum to a text field (your formula references a string field). However I can't calculate the median/percentiles on string values so how would you set the group sum to a number array? I'm not too familiar with arrays and variables so your help is greatly appreciated. Thanks.

 

[lbass]

I didn't mean for you to follow this literally. Why not show me what you have so far? I can't really help right now, but could look at it tomorrow.

-LB

 

[lbass]

In the week group section, add a formula like this to put the sums into an array for sorting:


//{@accum}:
whileprintingrecords;
numbervar i := i + 1;
numbervar array x;
redim preserve x;
x := sum({table.amt},{table.date},"weekly"); //your summary formula

In the report footer, add this formula which sorts the array and calculates the median:

//{@median}:
whileprintingrecords;
numbervar array x;
numbervar array y := 0;
Numbervar Array Final;
Numbervar m;
NumberVar n;
numbervar p := 0;
NumberVar MedianValue := 0;

for m := 1 to ubound(x) do (
if x[m] <> 0 then (
p := p + 1;
redim preserve y[p];
y[p] := x[m]
));
//sorting
for p := 1 to ubound do
(
redim preserve Final[ubound];
Final[p] := maximum;
for n := 1 to ubound do
(
if y[n] = Final[p] then
(
y[n] := 0;
Exit For;
);
);
);
if remainder(p-1,2)=1 then
MedianValue := Final[p/2] else
MedianValue := (Final[-int(-p/2)]+Final[int(p/2)])/2;

This averages the middle two values if the number of values is even.

Then add a second formula to the report footer to calculate the percentile. Note that this just returns the value in the array below which 25% of the values are found:

//{@25thpercentile}
evaluateafter({@med result});
whileprintingrecords;
numbervar array Final;
numbervar p;
numbervar pct := -(.25*(p-1)+.5); //minus to reverse the array order
Final[pct];

I'm not sure how you are defining the percentile, so you might have to adjust this.

-LB

 

[Karchan]

Thanks!!! That worked perfectly.... however I have a new dilemma now I have to add a new group to the existing report:

Program 1
08/29/2011-09/04/2011 100
09/05/2011-09/11/2011 80
09/12/2011-09/18/2011 94

Program 2
08/29/2011-09/04/2011 24
09/05/2011-09/11/2011 78
09/12/2011-09/18/2011 44

Etc...

How can I reset the median/percentile calculation for each Program (i.e. Group 1)?

Thanks in advance!

 

[lbass]

You would need a reset formula in the Program group header like this:

whileprintingrecords;
numbervar array x := 0;
numbervar array y := 0;
numbervar array Final := 0;
numbervar i := 0;

Then move the display formula to the group footer instead of the report footer.

If you need the median and the percentile in both group footer and report footer, you would have to leave the earlier formulas as is and create new formulas with separate variable names to get the group level names--consistently changing the names in all three formulas.

-LB

 

[Karchan]

Awesome!!! That worked perfectly, thanks a million.