Median on a Summary Value - Is this possible? 1

[bpunc]

How do I find the median of subtotals? Thanks in advance!

 

[Madawc]

In Crystal 8.5, you can do a running total that finds the median. It might not be able to pick up a subtotal as such: depending on how you did it, the value might not be known at the time when Crystal calculates running totals. You might find it necessary to go back to the original data.

Madawc Williams
East Anglia, Great Britain

 

[kenhamady]

I have never tried this, but I think you would have to create an array in memory, then load the subtotals into the array, and then do the mediam of the array. Could be challenging if you have never worked with array variables.

Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Expert's Guide to Formulas / Tips and Tricks / Guide to Crystal in VB

http://www.kenhamady.com/

- tek@kenhamady.com

 

[lbass]

You didn't say what you wanted to do with the median or where you would want to display it. A different approach would be to determine the median by doing a topN sort on the subtotal, and then creating a formula to be placed in the group header or footer:

if groupnumber = round(distinctcount({table.custID})/2,0) then Sum ({table.amount}, {table.custID}) //where {table.custID} is your group field

If you want to display the median in the report footer, you could change the formula to the following and place it in the group header or footer, wherever your subtotal is:

whileprintingrecords;
numbervar medianx;

if groupnumber = round(distinctcount({table.custID})/2,0) then medianx := Sum ({table.amount}, {table.custID}) else medianx := medianx;

And then place the following formula in the report footer:

whileprintingrecords;
numbervar medianx;

If you can't do a topN because of your desired report display, you could recreate the report in a subreport in the report header and do the above in the subreport.

-LB

 

[etseel]

I couldn't get that to work. Does anyone have another suggestion? Basically I want to calculate a summary function (median, mode, count, etc) on my group summary totals? E.g. "what is the average number of clients for my top 10 sales people", or "what is the median sales volume based on the volumes of my top 10 sales people?"

In a TopN report, I'm grouping based on sales rep - with a sum of their sales for the period specified. In the report footer, I want to display the median of the total sales values for our top 10 sales people (actually = the average of the 5th and 6th sales people in this case).

I tried to set up the formulas that LB posted, but it keeps telling me that it expects a boolean, etc.

Any ideas? Would that 3 formula trick work here? I understand how Arrays work in concept, but don't know how to program them in Crystal.

 

[lbass]

I double checked my formulas and they work. I suspect you didn't copy them exactly as they are in the post. But let me change the formula a little to allow for the median to fall between two sums (your specification of an average). Use the following formula in the group header or group footer, whereever you have your subtotals:

whileprintingrecords;
numbervar medianx;
numbervar ave1;
numbervar ave2;

if remainder(distinctcount({table.salesperson}),2) <> 0 then
(if groupnumber = round(distinctcount({table.salesperson})/2,0) then
medianx := sum({table.salesamt},{table.salesperson});

if remainder(distinctcount({table.salesperson}),2) = 0 then
(if groupnumber = distinctcount({table.salesperson})/2 then ave1 := sum({table.salesamt},{table.salesperson});

if groupnumber = (distinctcount({table.salesperson})/2) + 1 then ave2 := sum({table.salesamt},{table.salesperson});

medianx := (ave1 + ave1)/2);

Then in your report footer use:

whileprintingrecords;
numbervar medianx;

-LB

 

[etseel]

lbass, Thank you very much for your reply! I've learned a lot from many of your posts on this forum.

The updated version of the formula didn't quite work for me, but pointed me in a couple of useful directions. In my case, I was using a TopN n=20, so the distinctcounts don't match the groupnumbers very well. So my groupnumbers never get high enough to set the variables.

So my next stop was to ignore the distinctcounts and just set the groupnumber targets manually. As you programmed, the formula for median of an even number of values = Avg((N/2)+((N/2)+1)). So for a Top20 report, Median = (10th + 11th) values /2. (Think you meant to type (ave1 + ave2) in the last line in the group header). I ended up with this shortened version of your code:

whileprintingrecords;
numbervar medianx;
numbervar ave1;
numbervar ave2;

if groupnumber = 10 then ave1 := sum({Table.salesamt},{Table.salesperson});

if groupnumber = 11 then ave2 := sum({Table.salesamt},{Table.salesperson});

medianx := (ave1 + ave2)/2;

The code for the report footer was exactly as you posted:
whileprintingrecords;
numbervar medianx;

If I wasn't using a TopN report, I think your code would have worked with this minor tweaking:

whileprintingrecords;
numbervar medianx;
numbervar ave1;
numbervar ave2;

//This formula for median where sample size n is odd
if remainder(distinctcount({Table.salesperson}),2) <> 0 then
(if groupnumber = (((distinctcount({Table.salesperson})-1)/2)+1 then
medianx := sum({Table.salesamt},{Table.salesperson}));

//This formula for median where sample size n is even
if remainder(distinctcount({Table.salesperson}),2) = 0 then
(if groupnumber = distinctcount({Table.salesperson})/2 then ave1 := sum({Table.salesamt},{Table.salesperson}));

if groupnumber = (distinctcount({Table.salesperson})/2) + 1 then ave2 := sum({Table.salesamt},{Table.salesperson}));

medianx := ((ave1 + ave2)/2);

Ideally, what I'd like to do is base the median off the TopN value of N - but the only way I can figure out to do that is by using a parameter value {?TopN}. That way I can use the formula for median, mode, max, etc and save it to a repository so it works no matter what TopN value I pick... I can't seem to get it to work using a grand running total of gropunumber since the group running total changes on each pass. If anybody ever figures out a way to pass the N value of a TopN report directly into a formula, please let me know.

Thanks again LB...

 

[lbass]

There was a missing paren and the ave1 mistake that you found, and I think I didn't test it based on topN. The following tests out, if you can use a parameter {?topN} for the topN.

When you set up the topN, set it for &quot;All&quot; not the &quot;N&quot; you want, and instead, go to format->section->group header and group footer (I'm assuming details are suppressed)->suppress->x+2 and enter:

groupnumber > {?topN}

Groupnumber should work fine in these formulas UNLESS you have multiple groups. Let me know if that is the case. Otherwise, you can use:

{@medianx} for the group header or footer where your sums are:
whileprintingrecords;
numbervar medianx;
numbervar ave1;
numbervar ave2;

if remainder({?topN},2) <> 0 then
(if groupnumber = round({?topN}/2,0) then
medianx := sum({table.salesamt},{table.salesperson}));

if remainder({?topN},2) = 0 then
(if groupnumber = {?topN}/2 then ave1 := sum({table.salesamt},{table.salesperson});

if groupnumber = {?topN}/2 + 1 then ave2 := sum({table.salesamt},{table.salesperson});

medianx := (ave1 + ave2)/2);
medianx;

//{@displmedianx} for report footer:
whileprintingrecords;
numbervar medianx;

Hope this helps.

-LB