How do I use IIF to Avg - Max Points!

How do I rewrite this expression so that it ignores negitive numbers and zeros but will not return NaN... the expression below works great however if I change it to <>0 it returns NaN
= IIF(Fields!Profit.Value=0,nothing,(Fields!.Value/Fields!.Value))

Open in new window

LVL 1
SPLadyAsked:
Who is Participating?
 
cyberkiwiConnect With a Mentor Commented:
=IIF(Fields!Profit.Value=0,nothing,IIF(Fields!.Value/Fields!.Value)<=0,nothing,Fields!.value/Fields!.Value))

basically, for the 3rd part, wrap it in another IIF, test it for <= 0, and output either nothing or the 3rd part
0
 
sammySeltzerCommented:


Try this

= IIF(IsNothing(Fields!Profit.Value),0,Fields!.Value)/IIf(IsNothing(Fields!.Value) Or Fields!.Value=0,1,Fields!.Value)

0
 
SPLadyAuthor Commented:
Thank you @sammySeltzer I cant get it to work, I am lost on the denominator (bottom) part... below is the actual expression
IIF(Fields!InvoiceAmount_CurrentYear.Value=0,nothing,(Fields!ShipmentProfit.Value/Fields!InvoiceAmount_CurrentYear.Value))

Open in new window

0
Cloud Class® Course: MCSA MCSE Windows Server 2012

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

 
sammySeltzerCommented:
what do you want to show if negative?
0
 
sammySeltzerCommented:
and what error did you get with the code I posted?
0
 
cyberkiwiCommented:
Try changing from =0 to <= 0, so that if it is negative or 0, it goes to "nothing"

The problem with <>0 is that it causes divide by 0 on the right side
0
 
sammySeltzerCommented:
Try this.
Run this function

Public Shared Function Ratio(Num1 as double, Num2 as double) AS object
    IF ISNOTHING(Num2) Or Num2 = 0 Then
        Ratio = 0
    ELSEIF Num1 = 0 THEN
        Ratio = 0
    ELSE
        Ratio = Num1  / Num2
    END IF
End Function 

Open in new window


Then use this code to invoke the function:

=Code.Ratio(Fields!InvoiceAmount_CurrentYear.Value, Fields!ShipmentProfit.Value/Fields!InvoiceAmount_CurrentYear.Valu)

Open in new window

0
 
SPLadyAuthor Commented:
Ok I put the function in the report properties> code and I put the "invoker" in the expression... correct?

if so it indicates that the ratio portion is an unidentified expression

=Code.Ratio(Fields!InvoiceAmount_CurrentYear.Value, Fields!ShipmentProfit.Value/Fields!InvoiceAmount_CurrentYear.Valu)

0
 
cyberkiwiCommented:
Hi splady

"How do I rewrite this expression so that it ignores negitive numbers and zeros but will not return NaN... the expression below works great however if I change it to <>0 it returns NaN"

Do you want to turn
(1) a [final] result of -ve to 0, e.g. what the [working] IIF would return as-is right now?
or
(2) change the IIF such that the first condition ignores negative and 0? It currently doesn't perform the ratio for 0 [only], but that is only because the ratio of x/0 is undefined.  You cannot divide by 0.
0
 
SPLadyAuthor Commented:
I want the final result to be nothing or greater that Zero
0
 
SPLadyAuthor Commented:
Thank You!
0
 
sammySeltzerCommented:
hi SPLady,

Even though the thread is closed, I wanted to show you why you got undefined expression:

Fields!ShipmentProfit.Value/Fields!InvoiceAmount_CurrentYear.Valu)


Notice that I copied Fields!InvoiceAmount_CurrentYear.Valu but mistakenly cut off e.

So instead of Fields!InvoiceAmount_CurrentYear.Valu
e
I pasted Fields!InvoiceAmount_CurrentYear.Valu

sorry about that.
0
 
SPLadyAuthor Commented:
@sammySeltzer, thanks soo much, I missed that!
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.