6 Replies Latest reply: Dec 1, 2011 11:53 AM by Adrian Dogaru RSS

    How to ignore negative values in a Pivot Table

    Adrian Dogaru

      Hello; I am new to the QlickView application and I am facing an issue that should be pretty simple to resolve, however, so far I was unable to.

       

      Here is a simple example of what I would like to accomplish; I have 2 tables with the following data:

       

       

      IDTBL1.Col1
      110
      120
      1-5
      210
      220
      25

       

      IDTBL2.Col2
      12
      13
      14
      22
      23
      24

       

      I have a Pivot Table with ID as Dimension and with expressions: Sum(TBL1.Col1*TBL2.Col2). My Pivot table show like this:

      IDColA
      16
      2100

       

      While this is mathematically correct of course, my particular need is to ignore the negative value while processing the rest of the rows so that my result would be 80 in case of ID1.

       

      I have tried the following, but it does not give me the result I want: If (Sum(Col1*Col2)>0, Sum(Col1*Col2), 0)

      For all the negative value I would get a 0, whereas if I have no negative values I would get the correct result as seen below:

      IDColA
      10
      2100

       

       

      Also, I would like to avoid using an IF condition in the SQL statement, as those numbers may be included in another straight Table.

       

      Any suggestions would be extremely appreciated; thank you in advance!