Solved

How do I use IIF to Avg - Max Points!

Posted on 2010-11-17
13
303 Views
Last Modified: 2012-05-10
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

0
Comment
Question by:SPLady
  • 5
  • 5
  • 3
13 Comments
 
LVL 28

Expert Comment

by:sammySeltzer
Comment Utility


Try this

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

0
 
LVL 1

Author Comment

by:SPLady
Comment Utility
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
 
LVL 28

Expert Comment

by:sammySeltzer
Comment Utility
what do you want to show if negative?
0
 
LVL 28

Expert Comment

by:sammySeltzer
Comment Utility
and what error did you get with the code I posted?
0
 
LVL 58

Expert Comment

by:cyberkiwi
Comment Utility
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
 
LVL 28

Expert Comment

by:sammySeltzer
Comment Utility
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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 1

Author Comment

by:SPLady
Comment Utility
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
 
LVL 58

Expert Comment

by:cyberkiwi
Comment Utility
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
 
LVL 1

Author Comment

by:SPLady
Comment Utility
I want the final result to be nothing or greater that Zero
0
 
LVL 58

Accepted Solution

by:
cyberkiwi earned 500 total points
Comment Utility
=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
 
LVL 1

Author Closing Comment

by:SPLady
Comment Utility
Thank You!
0
 
LVL 28

Expert Comment

by:sammySeltzer
Comment Utility
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
 
LVL 1

Author Comment

by:SPLady
Comment Utility
@sammySeltzer, thanks soo much, I missed that!
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
This video discusses moving either the default database or any database to a new volume.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now