
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
How to show only negative values in a pivot/straight table?
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.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
if you do the sum of (turnover-cost) in Load by creating an extra field it will reduce lot of executations from object

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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() ?!).


- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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.
