Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 397
  • Last Modified:

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
0
mcse2007
Asked:
mcse2007
  • 7
  • 6
2 Solutions
 
mlmccCommented:
How large are the numbers?

Can any of the values be NULL?

What is this formula?
{@sum_condition_value}

mlmcc
0
 
GJParkerCommented:
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
 
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
[Webinar] Database Backup and Recovery

Does your company store data on premises, off site, in the cloud, or a combination of these? If you answered “yes”, you need a data backup recovery plan that fits each and every platform. Watch now as as Percona teaches us how to build agile data backup recovery plan.

 
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
 
mlmccCommented:
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
 
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

Featured Post

[Webinar] Database Backup and Recovery

Does your company store data on premises, off site, in the cloud, or a combination of these? If you answered “yes”, you need a data backup recovery plan that fits each and every platform. Watch now as as Percona teaches us how to build agile data backup recovery plan.

  • 7
  • 6
Tackle projects and never again get stuck behind a technical roadblock.
Join Now