7 Replies Latest reply: Jun 6, 2012 12:12 PM by Donna Vincent RSS

    Nested IF while, ignore negative values with exception

    Adrian Dogaru

      Hello everyone,

       

      I have a special case/user requirement that in a pivot table I would show a SUM, ignore negative values from the source table BUT make one exception.

      I believe a nested IF should do the trick, but all my tries so far failed; I got a bunch of different results... except of course, the one I'm looking for :-).

       

      It looks like my syntax is correct, but my logic is wrong; I was wondering if the Community may be able to point me in the right direction.

       

      SOURCE TABLE 1

      IDValue1
      110
      220
      31

       

      SOURCE TABLE 2

      IDValue 2
      110
      2-10
      3-20

       

      My Pivot Table is simple:

      Dimmension: ID

      Expression: Sum(Value1 * Value2)

       

       

      The desired Pivot Table would look like this:

      Header 1Header 2
      1100
      20
      3-20

       

      Basically:

      1. IF(Sum(Value1*Value2)<=0,0,Sum(Value1*Value2))

      2. IF(Table2.ID = '3', Sum(Value1*Value2))

       

      How do I combine the two IFs so that it will ignore (0) all negative values in SOURCE TABLE 2 except where ID=3?

       

      Thank you very much in advance