Formula issue?

Hi CR guru

The below formula produces 0.00 if has not value and I cannot find a feature in CR to supress if Zero value. If you amend the formula to produce a BLANK outcome rather than 0.00, what would the formula be? I think the way the formula field formatted is not number hence the feature 'supress value if zero' isn't available?

if Val( {@sum_condition_value} ) <= 0 then
    ""
else

Local StringVar outstring := {@sum_condition_value};
If Left(outstring ,1) = '-' then
    ""
Else
   CStr(Val(outstring ) * .1, 2)


Thanks heaps
LVL 7
mcse2007Asked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
mlmccConnect With a Mentor Commented:
Why are you converting the summary to a string?
Try it this way

If sum ({Sheet1_.Condition value}, {Sheet1_.Bill#Doc#})  >  0 Then
       sum ({Sheet1_.Condition value}, {Sheet1_.Bill#Doc#})   / 10
Else
       0

Or if you really want to use strings
If {@sum_condition_value} =  "0.00"  Then
   ""
Else If Left({@sum_condition_value},1) =  "-"  Then
   ""
 Else
      CStr(Val({@sum_condition_value})/10,2)

mlmcc
0
 
mlmccCommented:
How large are the numbers?

Can any of the values be NULL?

What is this formula?
{@sum_condition_value}

mlmcc
0
 
GJParkerConnect With a Mentor Commented:
Try this formula

If {@sum_condition_value} >  0 Then
       {@sum_condition_value}/10
Else
       0

Thsi shoukld return you the same results but as a number which you can then format and suppress zero values

HTH
0
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
mcse2007Author Commented:
hi mlmcc,  here it is...it is just summing the condition value by bill doc number.

CStr(sum ({Sheet1_.Condition value}, {Sheet1_.Bill#Doc#}),2,'')


------------------------------------------------

hi GJPParket, after ELSE, there is still condition. CR won't allow the formula to be saved because it say that Local String etc...isn't part of the formula but obviously it is.

If {@sum_condition_value} >  0 Then
       {@sum_condition_value}/10
Else
       0

Local StringVar outstring := {@sum_condition_value};
If Left(outstring ,1) = '-' then
    ""
Else
   CStr(Val(outstring ) * .1, 2)
0
 
mlmccCommented:
Is that 2 formulas or 1?

In Crystal syntax if that is a single formula it is not part of the formula because the formula ends at the 0.  To continue the formula you need a ; after the 0

This is the only formula you need as stated above by GJParker
If {@sum_condition_value} >  0 Then
       {@sum_condition_value}/10
Else
       0

You can then format it as a number and suppres 0s in the CUSTOM format.

If you want to do it with strings then
If {@sum_condition_value} >  0 Then
       CStr({@sum_condition_value}/10,2)
Else
       ""

mlmcc

0
 
mcse2007Author Commented:
hi mlmcc, when I use both of the above formula i get "A string is required here"...it is highlighting the Zero (0) after the greater symbol (>).
0
 
mcse2007Author Commented:
Hello mlmcc,

What this formula means?

Else If Left({@sum_condition_value},1) =  "-"  Then
   ""
 Else
      CStr(Val({@sum_condition_value})/10,2)

Could you break it down please for a novice guy like me.

Thanks
0
 
mlmccCommented:
There is only 1 formula

If {@sum_condition_value} =  "0.00"  Then
   ""
Else If Left({@sum_condition_value},1) =  "-"  Then
   ""
 Else
      CStr(Val({@sum_condition_value})/10,2)

If the string is 0.00 then display nothing
else test if it starts with a - if so then display nothin
otherwise disply the value

mlmcc
0
 
mcse2007Author Commented:
Ah...what is the below which still part of the formula

Val

CStr

/10,2

Sorry for asking these very basic and annolying questions which I find very intriguing. (which You probably thinking I'm clueless...which you are probably right)  
0
 
mlmccCommented:
CStr(Val({@sum_condition_value})/10,2)

CStr - Convert what is inthe ( ) to a string

Val - Convert the string to a numeric value

/10 - divide the numeric value by 10

2 - convert the string to have 2 decimal places

mlmcc
0
 
mcse2007Author Commented:
What's the logic behind dividing the numeric value by 10?
0
 
mcse2007Author Commented:
I forgot to thank you from your last post...learned again from you. so thank again.
0
 
mlmccCommented:
I dont know, you were doing it in the original formula.

It is the same as multiplying by 0.1

mlmcc
0
 
mcse2007Author Commented:
thanks mlmcc.........you're a legend !
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.