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

[#1]

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.

[#2]

if you do the sum of (turnover-cost) in Load by creating an extra field it will reduce lot of executations from object

[#3]

Even if I do this, it will still take too much time for the expressions to be calculated. Maybe there exist other possibilities of writing the expressions using other functions. I remember that I have used some time ago a function to do something similar, but for a pie chart, yet I don't remember exactly what the function was (maybe rangesum() ?!).

[johnw]

Rangesum() is pretty much like doing a series of +, except that if a value is null, it is treated as 0. I'm not seeing how to apply that here.

One possibility would be to use a calculated dimension. I'm probably getting my syntax wrong, but something like this:

if(aggr(sum(turnover-cost),article)<0 and aggr(sum(turnover),article)>0,article)

You still have the IF conditions, but only have to do them once per row instead of once per cell.