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?

15 Replies
pljsoftware
Creator III
Creator III
Author

No problem.

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

alexpanjhc
Specialist
Specialist

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

see attached

pljsoftware
Creator III
Creator III
Author

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.

brenner_martina
Partner - Specialist II
Partner - Specialist II

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

pljsoftware
Creator III
Creator III
Author

Thank you very much, it works!