Solved

How do I use IIF to Avg

Posted on 2010-11-17
2
562 Views
Last Modified: 2012-05-10
Hi experts Max Points.. How do I change this expression so that it will not include the fields with Zero in  the averaging...
=Avg(CDec(IIF(IsNumeric(Trim(Replace(Replace(Fields!ShipmentPercentage.Value,"NaN",""),"%",""))), Trim(Replace(Replace(Fields!ShipmentPercentage.Value,"NaN",""),"%","")) ,  0.0 ) ))

Open in new window

0
Comment
Question by:SPLady
2 Comments
 
LVL 10

Accepted Solution

by:
itcouple earned 500 total points
ID: 34155712
Hi

Way to long expression for my to work out ;)

See below sample which should work.

=AVG(IIF(Fields!Field.value=0,nothing,Fields!Field.value)

I suggest to put your expression into calculated field in dataset (without AVG) just so you can refer to calculated field without building very very long expression in one place.


Hope that helps
Emil
0
 
LVL 1

Author Closing Comment

by:SPLady
ID: 34155847
Thank you Emil! Excellent :)
0

Featured Post

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Introduction Earlier I wrote an article about the new lookup functions (http://www.experts-exchange.com/A_3433.html) that ship with SQL Server 2008 R2.  In this article I’m going to show you another new feature of SSRS 2008 R2, this time in the vis…
A recent questions about how to add SSRS named instances, couldn't find any that talks about SQL server 2008, anyway I decided to help by creating some screen shots. The installation is straightforward, you just pop the SQL server 2008 installati…
Microsoft Active Directory, the widely used IT infrastructure, is known for its high risk of credential theft. The best way to test your Active Directory’s vulnerabilities to pass-the-ticket, pass-the-hash, privilege escalation, and malware attacks …
Established in 1997, Technology Architects has become one of the most reputable technology solutions companies in the country. TA have been providing businesses with cost effective state-of-the-art solutions and unparalleled service that is designed…

785 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