3 Replies Latest reply: Feb 2, 2010 5:43 PM by John Witherspoon RSS

    How to show only negative values in a pivot/straight table?

    banciu

      I have different figures about sales (turnover, cost) and I want to build a pivot or straight table where I want to show only articles with negative margin and positive turnover (negative turnover means product is returned by the customer).

      As dimensions, I will have Date and Article.

      I want to have 3 different expressions:

      1. Margin = if( sum(turnover-cost) < 0 and sum(turnover) >0, sum(turnover-cost))

      2. Margin % = if( sum(turnover-cost) < 0 and sum(turnover) >0, 1-sum(cost)/sum(turnover))

      3. Turnover = if( sum(turnover-cost) < 0 and sum(turnover) >0, sum(turnover))

      The expressions I wrote above are working just fine. The problem is that in the case of a greater amount of data, the calculation time is pretty long, i.e. I want to replace the heavy "IF" conditions with something else which is faster.