Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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:
Order | ValueA | ValueB |
---|---|---|
1 | 10 | 13 |
2 | 12 | 12 |
3 | 15 | 10 |
4 | 19 | 18 |
5 | 11 | 16 |
Where ValueA and ValueB are calculated values (using expressions).
I need to make the following computations:
In conclusion, I need to create two Text fields, one containing the value 8 and one with 6.
Is it possible?
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))
sum(if(ValueB>ValueA,ValueB-ValueA,0))
sum(if(ValueA>ValueB,ValueA-ValueB,0))
Thank you gwassenaar, but I forgot the most important detail: ValueA and ValueB aren't plain values, but are calculated values (using complex expressions).
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))
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.
Ok this is a bit more confusing to understand without data. Can you upload a sample app?
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
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.
Can you upload it here? I do not trust going to unknown websites to download. You can select Use advanced editior and upload it