Min, Max, Avg--HELP Please

[SMosley804]

Crystal Developer ver 11
DB-IBM DB2
-----------------------------------------------------------

I have 4 columns base off of the date field in order to get 4 columns,
1.day, 2.month, 3.week, 4.year


The formulas that populate these colums look like the following.

if {startdate} >=(currentdate-7) and {startdate} <currentdate then {Hours}

I end up with columns similar to the following ex.

start date Day Week Month

6/29/2008 0 0 0
6/30/2008 0 16.10 16.10
7/1/2008 0 292.91 292.91
7/2/2008 0 185.20 185.20
7/3/2008 0 227.35 227.35
7/4/2008 0 213.78 213.78
7/5/2008 0 220.12 220.12
7/6/2008 191.70 191.70 191.70
7/7/2008 0 118.23 118.23


I need to get the min max,and avg values for only set where there is data because the other dates don't apply.
I can get a max value but I am having problems getting the min and avg because it is including the zeroes which should not be included.

 

[SMosley804]

Actually, I am able to figure out the average and the max values, bu the min value is what I am having a problem with because of the zeroes which really don't mean anything.

 

[lbass]

Create a formula called {@null} by opening and saving a new formula without entering anything. Then change your conditional formulas to:

if {startdate} >=(currentdate-7) and
{startdate} <currentdate then
{Hours} else
tonumber({@null})

Then when you insert a minimum, the lowest value will appear. If you want 0 to appear, format each formula by using displaystring->x+2 and entering:

if isnull(currentfieldvalue) then
0 else
currentfieldvalue

-LB

 

[SMosley804]

Thanks LBASS,

When I use the displaystring formula that you listed above,

I get an error that says " afield is required here" referring to the currentfieldvalue.

Am I doing something wrong or is there a way I can modify this formula so that works.

 

[lbass]

Sorry, I didn't test that. I think you need to use my earlier formula with the {@null} to insert the groups (you can then suppress this), but then use your original formula for display.

-LB

 

[lbass]

PS. An alternative approach is to use running totals that summarize the column, with an evaluation formula of:

<field you are summarizing> <> 0

-LB