Solved

How do I use IIF to Avg

Posted on 2010-11-17
2
564 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

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SQL Server 2012 r2 - Make Temp Table Query Faster 5 50
SYbase 4 36
Awk & Pythagoras - applying csv file data to the problem 26 50
Comparison query - 4 columns 9 19
Introduction In the following article I’ll be discussing and demonstrating several different ways of how images can be put on a report. I’m using SQL Server Reporting Services 2008 R2 CTP, more precisely version 10.50.1352.12, but the methods ex…
Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…

789 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