Remove null values from a running total

[llunde]

I am trying to do a count using a Running Total Field in Crystal.

I would like it to count Order Numbers but it put zeros instead of blanks when there is no order number, so when I do the count it counts the zeros also.

I am evaluating on change of group and resetting on another change of group.

I tried to put a formula in to evaluate when not equal to zero, but then I am not evaluating in my change of group.

Thank you
LL

 

[lbass]

Create a formula {@null} by opening a new formula and saving it without entering anything. Then create a second formula:

if isnull({table.Order}) or
{table.Order} = 0 then
tonumber({@null}) else
{table.order}

Then use this as the field to count in your running total, instead of {table.Order}.

-LB

 

[llunde]

Thanks for your response.

I tried this but I couldn't get it to work.
I got an error "the string is non-numeric".

I did get another thing to work.

if {order} = 0 then "" else ToText {order}

The problem is the number is a format with a comma and .00 (example 1,234.00) I need it as just the number (1234).
So when I converted it to text I couldn't change the format of the number.
Any ideas.

Thank you,
LL

 

[lbass]

What is the datatype of your order field?

-LB

 

[llunde]

numeric

 

[lbass]

numeric isn't a datatype.

If it is a number, then:

if isnull({table.Order}) or
{table.Order} = 0 then
tonumber({@null}) else
{table.order}

...should have worked without error, so please show how you implemented this formula.

-LB