locked
Removing 'Infinity' from field RRS feed

  • Question

  • I have created a report, but i notice that i get 'Infinity' in my % column

    how do i remove this from view, i.e. to return a blank field

    below is the formula used to populate the % column

    =IIF(SUM(iif(Left(Fields!FinancialYear.Value,4) = "2007", 1, 0)) = 0 ,"%" ,(SUM(iif(Left(Fields!FinancialYear.Value,4) = "2008", 1, 0)) - SUM(iif(Left(Fields!FinancialYear.Value,4) = "2007", 1, 0))) / SUM(iif(Left(Fields!FinancialYear.Value,4) = "2008", 1, 0)))
    
    

     


    Friday, April 3, 2009 2:17 PM

Answers

  • =IIF(SUM(iif(Left(Fields!FinancialYear.Value,4) = "2007", 1, 0)) = 0 ,"%" ,Iff( (SUM(iif(Left(Fields!FinancialYear.Value,4) = "2008", 1, 0)) = 0, -1 , (SUM(iif(Left(Fields!FinancialYear.Value,4) = "2008", 1, 0)) - SUM(iif(Left(Fields!FinancialYear.Value,4) = "2007", 1, 0))) / IIf(SUM(iif(Left(Fields!FinancialYear.Value,4) = "2008", 1, 0)) = 0, 1, SUM(iif(Left(Fields!FinancialYear.Value,4) = "2008", 1, 0))))))

    I don' get the exact condition for which you want -100 % , may be above condtion work , try that
    ~~ Mark it as Answer if you find it correct~~
    Friday, April 3, 2009 3:40 PM

All replies

  • try this


    =IIF(SUM(iif(Left(Fields!FinancialYear.Value,4) = "2007", 1, 0)) = 0 ,"%" ,(SUM(iif(Left(Fields!FinancialYear.Value,4) = "2008", 1, 0)) - SUM(iif(Left(Fields!FinancialYear.Value,4) = "2007", 1, 0))) / IIf(SUM(iif(Left(Fields!FinancialYear.Value,4) = "2008", 1, 0)) = 0, 1, SUM(iif(Left(Fields!FinancialYear.Value,4) = "2008", 1, 0))))






    ~~ Mark it as Answer if you find it correct~~
    Friday, April 3, 2009 2:42 PM
  • it nearly works,

    the infinity value has disappeared, but the % value is far to large.

    for example -

    2007 = 10
    2008 = 0

    % = -1000%

    it should be -100% not -1000%???

    Friday, April 3, 2009 2:47 PM

  • Try this :-
    YOu may have to match Paranthisis.

    =IIF(SUM(iif(Left(Fields!FinancialYear.Value,4) = "2007", 1, 0)) = 0 ,"%" ,Iff( (SUM(iif(Left(Fields!FinancialYear.Value,4) = "2008", 1, 0)) = 0, 0 , (SUM(iif(Left(Fields!FinancialYear.Value,4) = "2008", 1, 0)) - SUM(iif(Left(Fields!FinancialYear.Value,4) = "2007", 1, 0))) / IIf(SUM(iif(Left(Fields!FinancialYear.Value,4) = "2008", 1, 0)) = 0, 1, SUM(iif(Left(Fields!FinancialYear.Value,4) = "2008", 1, 0))))))

    ~~ Mark it as Answer if you find it correct~~
    Friday, April 3, 2009 2:57 PM
  • still not doing what i need it to do

    this time it produces the following

    for example -

    2007 = 10
    2008 = 0

    % = %

    it should be -100% not %???

    thanks again
    Friday, April 3, 2009 3:02 PM
  • Tell me What exactly you trying to Achieve through this expression.
    If you are looking to try to calulcate % , better use % in Format tab.

    An in denominator Put if condition So to check for 0 as as denominator.
    ~~ Mark it as Answer if you find it correct~~
    Friday, April 3, 2009 3:04 PM
  • already have '#.#%' in the format tab
    Friday, April 3, 2009 3:10 PM
  • Try P1 if you want to Include till one decimal place.
    Also there will not be -100 % because
    If 2008 =0 Then this Will be 0 because your denominator evaluates to 0 if 2008 =0 as so do the numertaor.
    ~~ Mark it as Answer if you find it correct~~
    Friday, April 3, 2009 3:23 PM
  • can i right a piece of code to make it -100%
    Friday, April 3, 2009 3:37 PM
  • =IIF(SUM(iif(Left(Fields!FinancialYear.Value,4) = "2007", 1, 0)) = 0 ,"%" ,Iff( (SUM(iif(Left(Fields!FinancialYear.Value,4) = "2008", 1, 0)) = 0, -1 , (SUM(iif(Left(Fields!FinancialYear.Value,4) = "2008", 1, 0)) - SUM(iif(Left(Fields!FinancialYear.Value,4) = "2007", 1, 0))) / IIf(SUM(iif(Left(Fields!FinancialYear.Value,4) = "2008", 1, 0)) = 0, 1, SUM(iif(Left(Fields!FinancialYear.Value,4) = "2008", 1, 0))))))

    I don' get the exact condition for which you want -100 % , may be above condtion work , try that
    ~~ Mark it as Answer if you find it correct~~
    Friday, April 3, 2009 3:40 PM
  • Try this,

    =IIF(SUM(iif(Left(Fields!FinancialYear.Value,4) = "2007", 1, 0)) = 0 OR (SUM(iif(Left(Fields!FinancialYear.Value,4) = "2008", 1, 0)) - SUM(iif(Left(Fields!FinancialYear.Value,4) = "2007", 1, 0))) / SUM(iif(Left(Fields!FinancialYear.Value,4) = "2008", 1, 0)) ="Infinity" , "%"  , (SUM(iif(Left(Fields!FinancialYear.Value,4) = "2008", 1, 0)) - SUM(iif(Left(Fields!FinancialYear.Value,4) = "2007", 1, 0))) / SUM(iif(Left(Fields!FinancialYear.Value,4) = "2008", 1, 0)))

     


    Rajesh Jonnalagadda http://www.ggktech.com
    Saturday, April 4, 2009 5:53 AM
    Answerer