10 Replies Latest reply: Oct 12, 2012 2:20 PM by Trevor Roth RSS

    if-statement expression not aggregating correctly

    Trevor Roth

      Can someone help me with a percentage calculation that is not aggregating correctly? My expressions are using if-statements to filter out rows in a chart accordingly. [DPC %] is calculating correctly for each individual line in the chart, but the total for the expression is wrong. The total for [DPC %] is including rows in the chart are filtered out, so it is ignoring the if-statement. I believe this is because my expressions use sum(x) inside the if-statement – but I don’t know how else to do this expression. Any ideas?


      right.png


      wrong.png


      Expressions:


      Net Sales:


      =if(vSalesFilter_UseTheFilterFlag='Yes', 

          if(sum([Net Sales]) >= vSalesFilter, sum([Net Sales])),

          sum([Net Sales]))



      DPC $


      =if(vSalesFilter_UseTheFilterFlag='Yes', 

          if(sum([Net Sales]) >= vSalesFilter, sum([DPC])),

          sum([DPC]))



      DPC %


      =

      if(vSalesFilter_UseTheFilterFlag='Yes', 

          if(sum([Net Sales]) >= vSalesFilter, sum([DPC])),

          sum([DPC]))

      /    

      if(vSalesFilter_UseTheFilterFlag='Yes', 

          if(sum([Net Sales]) >= vSalesFilter, sum([Net Sales])),

          sum([Net Sales]))