Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
pljsoftware
Creator III
Creator III

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?

1 Solution

Accepted Solutions
Not applicable

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))

View solution in original post

15 Replies
Gysbert_Wassenaar

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

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


talk is cheap, supply exceeds demand
pljsoftware
Creator III
Creator III
Author

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

Not applicable

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))

pljsoftware
Creator III
Creator III
Author

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.

Not applicable

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

jonathandienst
Partner - Champion III
Partner - Champion III

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

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
pljsoftware
Creator III
Creator III
Author

Sure!

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

Thank you very much for you support!

pljsoftware
Creator III
Creator III
Author

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.

Not applicable

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