topic Re: Sum of only certain values in QlikView

Sum of only certain values

pljsoftware — Thu, 06 Sep 2012 13:46:17 GMT

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:

Calculate the SUM of the difference for the rows where ValueB is grater than ValueA, in this case (13-10)+(16-11) = 8
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

Gysbert_Wassenaar — Thu, 06 Sep 2012 13:50:23 GMT

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

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

Re: Sum of only certain values

pljsoftware — Thu, 06 Sep 2012 13:54:19 GMT

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

Thu, 06 Sep 2012 14:04:55 GMT

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

pljsoftware — Thu, 06 Sep 2012 14:13:34 GMT

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

Thu, 06 Sep 2012 14:16:59 GMT

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

Re: Sum of only certain values

jonathandienst — Thu, 06 Sep 2012 14:50:52 GMT

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

pljsoftware — Thu, 06 Sep 2012 14:52:18 GMT

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

pljsoftware — Thu, 06 Sep 2012 15:17:00 GMT

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

Thu, 06 Sep 2012 15:21:29 GMT

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

pljsoftware — Thu, 06 Sep 2012 15:38:09 GMT

No problem.

Re: Sum of only certain values

Thu, 06 Sep 2012 16:08:16 GMT

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

alexpanjhc — Thu, 06 Sep 2012 19:46:12 GMT

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

pljsoftware — Fri, 07 Sep 2012 08:08:16 GMT

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

brenner_martina — Fri, 07 Sep 2012 09:42:16 GMT

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

Re: Sum of only certain values

pljsoftware — Fri, 07 Sep 2012 09:43:41 GMT

Thank you very much, it works!