How to use MEDIAN function, but not count zeros

[danhauer]

I have to find the median number in columns of data, without counting zeroes. The zeroes are the result of a formula. I tried saying if {@days} > 0, then {@days} else null or "" but that didn't work because of course, the formula expects a numeric value.

So my report looks like:

JAN FEB MAR
0 0 9
7 0 0
2 0 0
0 4 0
0 0 5
0 2 0
1 0 0
0 2 0

Median 0 0 0

Any ideas?

Thank you,

danhauer

 

[ianoctdec]

Hello,

You could try this(found it on this forum):
- create an empty formula, @ZeroDays
- then create formula:
@ValidDays
if @days = 0 then
@ZeroDays
else
@days

Use this formula to calculate your median.

Daniela

 

[danhauer]

Hi Daniela - I tried something like that and still got the error "A number is required here". :-(

if month({@DDateClosed}) = 1 then {@DaysOpen} else {@Dummy}

Thanks for the response, though.

If I can't figure this out, I think I will group by month, which should fix it but the format won't be exactly how the customer wants it....

 

[lbass]

A formula saved and closed with nothing in it, which I usually call {@Null}, is a string, so try the following instead:

if month({@DDateClosed}) = 1 then
{@DaysOpen} else tonumber({@Null})

-LB

 

[ianoctdec]

oh, yes, @Dummy is a string. Change it like this:
if month({@DDateClosed}) = 1 then
{@DaysOpen}
else
tonumber({@Dummy})

Daniela

 

[lbass]

Actually that's not correct. Create a formula {@NonZeroDays}:

if {@Days} > 0 then
{@Days} else
tonumber({@Null})

Then for January, use:

if month({@DDateClosed}) = 1 then
{@NonZeroDays} else
tonumber({@Null})

Insert the median on {@January}.

-LB

 

[kenhamady]

What I do to generate a numeric NULL is to create a formula called Null and (temporarily) put a zero in it. Then, after you have used the Null formula in another formula as a numeric value, you can go back and delete the zero and save the formula empty. The data type will stay Numeric but the value is NULL which prevents it from affecting your Median.

Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Guides to Formulas, Parameters, Subreports, Cross-tabs, VB, Tips and Tricks

http://www.kenhamady.com/

 

[danhauer]

Thank you all SOOOO much!! I was able to get the report to the customer this morning - - in the format they wanted!!

I did have to use Ken's tip to create a formula with a zero value, use it, then remove the zero and save. Thanks again and again.

 

[lbass]

I just tested this and using Ken's approach or my approach (tonumber({@null}) results in identical numbers, so either way works.

-LB