Formula for minimum excluding zeros

[lisze]

I don't understand how the minimum formula works or whether it can be applied in this report. I'm using CR7 and the report groups by UserName.

I have formula {@A} that stores the call durations for all inbound calls:
If {@CallDirection} = "Inbound" Then ({@CallDur}/60) Else 0

What I'd like to have is the minimum call duration for each user excluding zeros displayed at the Group Footer section. This is the {@MinDur} formula:

Global NumberVar x;
Global NUmberVar grpNo;

If OnFirstRecord Then
(
grpNo := GroupNumber;
x := {@A};
)
Else If grpNo <> GroupNUmber Then
(
grpNo := GroupNumber;
x := {@A};
);

If {@A} <> 0 Then
(
If {@A} < x Then
x := {@A};
);

x;

I have six user records in the database but only 4 is showing. The {@mindur} for 3 users are correct but one is wrong. I'm not sure if the formula is correct. Please advise. Thanks.

 

[Ngolem]

I think that your problem is that {@A} can be zero on the first record using this formula

If {@CallDirection} = &quot;Inbound&quot; Then ({@CallDur}/60) Else 0


Thereafter...no other formula can be less than that value hence you will get no minimum using this method.

Perhaps you could make the formula like this:

Global NumberVar x;
Global NUmberVar grpNo;

If OnFirstRecord Then
(
grpNo := GroupNumber;
if {@A} = 0 then
x := 99999 //a ridiculous high number
else
x := {@A};
)
Else If grpNo <> GroupNUmber Then
(
grpNo := GroupNumber;
if {@A} = 0 then
x := 99999 //a ridiculous high number
else
x := {@A};

If {@A} <> 0 Then
(
If {@A} < x Then
x := {@A};
);

x;

This might work better for you

Jim

 

[chelseatech]

Conditional running totals might be a useful solution.

Select your formula as the field to sumamrise and &quot;Minimum&quot; as the summary operation.
Evaluate - based opn a formula of
{@CallDur}<> 0
Editor and Publisher of Crystal Clear

http://www.chelseatech.co.nz/pubs.htm

 

[lisze]

The conditional running total for the inbound calls is correct only for the first user in the group. Subsequent groups shows either zero or blank. I reset the conditional running total on change of UserName. I created a similar conditional running total for outbound calls which is incorrect showing only either zeros or blank. I think the blanks may be because there are no values for to summarize on.

Jim,
The modified formula actually works correctly for the inbound calls {@InMinDur}. When I created another similar formula for outbound calls, the results are all 0. I tried using global variables that are different from {@InMinDur} but I still get all zeros.

Thanks!

 

[lisze]

Any ideas why I still get zeros?

 

[chelseatech]

I suspect nulls are casuing your problem - they can mess up the running total.

So create a formula
if isnull({table.field}) then 0 else {table.field}

And use that as the field to summarise in the running total. Editor and Publisher of Crystal Clear

http://www.chelseatech.co.nz/pubs.htm