15 Replies Latest reply: Sep 7, 2012 5:42 AM by Martina Brenner

# Sum of only certain values

Hi!

Excuse me for the title of the post, but I don't know how to summarize my problem.

Basically, I have a table such this one:

OrderValueAValueB
11013
21212
31510
41918
51116

Where ValueA and ValueB are calculated values (using expressions).

I need to make the following computations:

1. Calculate the SUM of the difference for the rows where ValueB is grater than ValueA, in this case (13-10)+(16-11) = 8
2. Calculate the SUM of the difference for the rows where ValueA is grater than ValueB, in this case (15-10)+(19-18) = 6

In conclusion, I need to create two Text fields, one containing the value 8 and one with 6.

Is it possible?

• ###### Re: Sum of only certain values

sum(if(ValueB>ValueA,ValueB-ValueA,0))

sum(if(ValueA>ValueB,ValueA-ValueB,0))

• ###### Re: Sum of only certain values

Thank you gwassenaar, but I forgot the most important detail: ValueA and ValueB aren't plain values, but are calculated values (using complex expressions).

• ###### Re: Sum of only certain values

What are the expressions? You can probably do something using Aggr function to get the totals per dimension.

sum(Aggr( if( ExpressionB>ExpressionA,ExpressionB-ExpressionA,0),Order))

sum(Aggr( if( ExpressionA>ExpressionB,ExpressionA-ExpressionB,0),Order))

• ###### Re: Sum of only certain values

The expressions is something like these:

Expression A

Sum(GiornateLavorateSettore)/GetPossibleCount(Giorno)

Expression B

if(IsNull(DataOraInizioPrecoordinamento)

,sum(if(DataOraMinuto>= DataOraInizioPrecoordinamento and DataOraMinuto<= DataOraFinePrecoordinamento, QtaPrecoordinamento))

,0)

/(480*GetPossibleCount(Giorno))

And the aggregation must be done using the DataOraMinuto dimension itself.

• ###### Re: Sum of only certain values

Ok this is a bit more confusing to understand without data. Can you upload a sample app?

• ###### Re: Sum of only certain values

Sure!

You can find a sample app here: http://sdrv.ms/Sn2F5S.

Thank you very much for you support!

• ###### Re: Sum of only certain values

Can you upload it here? I do not trust going to unknown websites to download. You can select Use advanced editior and upload it

• ###### Re: Sum of only certain values

No problem.

• ###### Re: Sum of only certain values

Ok try this:

First B>A:

sum(Aggr( if( (if(IsNull(DataOraInizioPrecoordinamento),sum(if(DataOraMinuto>= DataOraInizioPrecoordinamento and DataOraMinuto<= DataOraFinePrecoordinamento, QtaPrecoordinamento)),0))>(sum(if(DataOraMinuto>= DataOraEntrataTimbraturaSettore and DataOraMinuto<= DataOraUscitaTimbraturaSettore, 1))),(if(IsNull(DataOraInizioPrecoordinamento),sum(if(DataOraMinuto>= DataOraInizioPrecoordinamento and DataOraMinuto<= DataOraFinePrecoordinamento, QtaPrecoordinamento)),0))-(sum(if(DataOraMinuto>= DataOraEntrataTimbraturaSettore and DataOraMinuto<= DataOraUscitaTimbraturaSettore, 1))),0),DataOraMinuto))

Second A>B:

sum(Aggr((sum(if(DataOraMinuto>= DataOraEntrataTimbraturaSettore and DataOraMinuto<= DataOraUscitaTimbraturaSettore, 1)))>

if((if(IsNull(DataOraInizioPrecoordinamento)

,sum(if(DataOraMinuto>= DataOraInizioPrecoordinamento and DataOraMinuto<= DataOraFinePrecoordinamento, QtaPrecoordinamento))

,0)),(sum(if(DataOraMinuto>= DataOraEntrataTimbraturaSettore and DataOraMinuto<= DataOraUscitaTimbraturaSettore, 1))) -

(if(IsNull(DataOraInizioPrecoordinamento)

,sum(if(DataOraMinuto>= DataOraInizioPrecoordinamento and DataOraMinuto<= DataOraFinePrecoordinamento, QtaPrecoordinamento))

,0)),0),DataOraMinuto))

• ###### Re: Sum of only certain values

Thank you very much, it works!

• ###### Re: Sum of only certain values

Hi

If I have understood you correctly, you can use the following two expressions:

For "Sum If (Expr2 > Expr1, Expr2 - Expr1)":

```Sum(Aggr(
RangeMax(
(If(IsNull(DataOraInizioPrecoordinamento),
Sum(If(DataOraMinuto >= DataOraInizioPrecoordinamento and DataOraMinuto <= DataOraFinePrecoordinamento, QtaPrecoordinamento))
,0
) / (480 * GetPossibleCount(Giorno))
-
Sum(GiornateLavorateSettore) / GetPossibleCount(Giorno)),
0
),
Order))
```

For "Sum If (Expr1 > Expr2, Expr1 - Expr2)":

```Sum(Aggr(
RangeMax(
(Sum(GiornateLavorateSettore) / GetPossibleCount(Giorno)
-
If(IsNull(DataOraInizioPrecoordinamento),
Sum(If(DataOraMinuto >= DataOraInizioPrecoordinamento and DataOraMinuto <= DataOraFinePrecoordinamento, QtaPrecoordinamento))
,0
) / (480 * GetPossibleCount(Giorno))),
0
),
Order))
```

Apart from simplifying the expression, using RangeMax to enforce the expr1>expr2 and expr2>expr1 conditions means that everything is calculated only once.

Hope that helps

Jonathan

• ###### Re: Sum of only certain values

Unfortunately, it doesn't seem to work. If you could take a look to the real example I have posted before, I really appreciate it.

Regards.

• ###### Re: Sum of only certain values

i think you can just use if (column(1)>=column(2),column(1)-column(2), column(2)-column(1))

see attached

• ###### Re: Sum of only certain values

Thank you for the answer but, as I told in my first post, I need to show these values inside two Text fields, not in the table.

• ###### Re: Sum of only certain values

Hi,

what about using relative Columns

sum(if(Column(2)>Column(1),Column(2)-Column(1),0))+

sum(if(Column(1)>Column(2),Column(1)-Column(2),0))

Greetings from Munich

Martina