topic Re: Problem with columns and Sum Total in QlikView

Problem with columns and Sum Total

pgalvezt — Mon, 27 Jan 2014 13:24:36 GMT

Hello,

I have many Expressions. Many of them works fine but some columns shows "-" and doesn't sum too whe the values are correct.

I attached a file

Thanks.

Re: Problem with columns and Sum Total

jerem1234 — Mon, 27 Jan 2014 13:35:06 GMT

This is because you are referencing the same column as the expression. Therefore since there is no value yet for that column, it will return null. I think what you want to do is decrease each number by 1, for example:

column(4)*column(8)

should be:

column(4)*column(7)

Hope this helps!

Re: Problem with columns and Sum Total

Colin-Albert — Mon, 27 Jan 2014 13:36:20 GMT

The expression below for "Cantidad < 1 AÑO" refers to a missing field.

Your data model does not have a field Nuevo1, but does have Nuevo, nuevo3, nuevo4

Field names is QlikView and data are all case sensitive so a filed called nuevo3 is a different field to Nuevo3

Also you will need to use Aggr() for the sums in the if statement to define which dimensions the sum is being calculated for.

=if (Nuevo = 1, If( (aggr(Sum(cantidad_oh), articulo ) - aggr(Sum(patrimonio), articulo )) < 0, '0', Sum(cantidad_oh) - Sum(patrimonio)), '0')

Re: Problem with columns and Sum Total

Anonymous — Mon, 27 Jan 2014 13:37:26 GMT

Pablo,

I see that the expression "Cantidad < 1 AÑO" is using field Nuevo1 which doesn't exist. Try to fix it first. Other expressions maybe refer to this one.

Regards,

Michael

Re: Re: Problem with columns and Sum Total

pgalvezt — Mon, 27 Jan 2014 13:53:44 GMT

Jerem I think the expression is ok. Its Column 4 * Column 8

Column(4) Column (8)

If(Sum(cantidad_oh) = 0, costo2, Sum(valor_oh)/Sum(cantidad_oh)) * (if (nuevo = 1, If(Sum(cantidad_oh) - Sum(patrimonio) < 0, '0', Sum(cantidad_oh) - Sum(patrimonio)), '0')).

Colin And Michael My Mistake I was changing the field. Now its ok. Why the totals gives 0? I attached a new example

Thanks!!

Re: Re: Problem with columns and Sum Total

Anonymous — Mon, 27 Jan 2014 14:08:07 GMT

I can see that the value of nuevo is always 0. As a result, column 8 is always zero, and colaumn(4) * column(8) is 0 as result.

Re: Re: Re: Problem with columns and Sum Total

pgalvezt — Mon, 27 Jan 2014 14:21:49 GMT

Thank you for your Reply,

This is because exist reduction data and have a file with low weight. Now you can see 0 and 1. My totals are 0 You know could be?

Thanks.

Re: Re: Re: Re: Problem with columns and Sum Total

Anonymous — Mon, 27 Jan 2014 14:28:24 GMT

The nuevo is still only 0. Bu I tried something, just for testing, changed the expression in column 8 to

if (nuevo = , If(Sum(cantidad_oh) - Sum(patrimonio) < 0, '0', Sum(cantidad_oh) - Sum(patrimonio)), '0')

and, the result in the next column change to non-0.

Re: Re: Re: Re: Re: Problem with columns and Sum Total

pgalvezt — Mon, 27 Jan 2014 14:44:22 GMT

Sorry wrong file.

I tried your formula but gave me an error.

Re: Problem with columns and Sum Total

Anonymous — Mon, 27 Jan 2014 14:58:19 GMT

For some reason the "0" was lost in my post:

if(nuevo=0...

As for the total - to make it work your expression must be aggregated function. Currently it is not. This is a correct one:

sum(aggr(

if (nuevo = 1, If((aggr(Sum(cantidad_oh),articulo) - aggr(Sum(patrimonio),articulo)) < 0, '0', Sum(cantidad_oh) - Sum(patrimonio)), '0')

, articulo))

Re: Problem with columns and Sum Total

pgalvezt — Mon, 27 Jan 2014 18:13:19 GMT

Many Thanks Michael.

I tried of follow with the others expression but doesn´t work. Can you help me with the big one (Cantidad Entre 0 - 1 AÑO), And can follow with the others.

Thanks again!

Re: Problem with columns and Sum Total

Anonymous — Mon, 27 Jan 2014 19:21:46 GMT

I'll give you this one, but you have to figure out the rest..

First, I re-wrote the expression to make it look simpler. Logic is the same, please verify:
if(Column(8)=0 AND Column(6))>0 AND (Sum(cantidad2_oh)-Sum(patrimonio))>=0 AND (Sum(cantidad2_oh)-Sum(patrimonio))<=Column(6), (Sum(cantidad_oh)-Sum(patrimonio)), 0)

Next, I replaced Column(6) and Column(8) with their actual expressions:

if(if (nuevo = 1, If((aggr(Sum(cantidad_oh),articulo) - aggr(Sum(patrimonio),articulo)) < 0, '0', Sum(cantidad_oh) - Sum(patrimonio)), '0')=0 AND Sum(consumo)>0 AND (Sum(cantidad2_oh)-Sum(patrimonio))>=0 AND (Sum(cantidad2_oh)-Sum(patrimonio))<=Sum(consumo), (Sum(cantidad_oh)-Sum(patrimonio)), 0)

So far, it returns the same result as your expression.

And, the last step was the same as before, adding sum(aggr()):

sum(aggr(

if(if (nuevo = 1, If((aggr(Sum(cantidad_oh),articulo) - aggr(Sum(patrimonio),articulo)) < 0, '0', Sum(cantidad_oh) - Sum(patrimonio)), '0')=0 AND Sum(consumo)>0 AND (Sum(cantidad2_oh)-Sum(patrimonio))>=0 AND (Sum(cantidad2_oh)-Sum(patrimonio))<=Sum(consumo), (Sum(cantidad_oh)-Sum(patrimonio)), 0)

, articulo))

Regards,

Michael

Re: Problem with columns and Sum Total

pgalvezt — Mon, 27 Jan 2014 20:10:41 GMT

Thats the idea many thanks Michael!!

Re: Problem with columns and Sum Total

pgalvezt — Tue, 28 Jan 2014 17:52:15 GMT

Hi Michael,

I have proved some formulas but I fail to multiply columns. Can you help me with this?

I have tried first:

Column(4) * Column(8) nothing

Sum(Column(4) * Column(8)) Exceed Memory

Sum(Column(4)) * Sum(Column(8)) Exceed Memory

With Aggr I fail too.

Thanks!

Re: Re: Problem with columns and Sum Total

pgalvezt — Tue, 28 Jan 2014 18:44:02 GMT

Michael,

My formula It was fine. I dont Know QlikView Displayed a $ as total (Weird Right?). When Export to excel Appear the final result.

$ 1.139.920.299.839.620

I attached the problem.

Thank you for your help.

Re: Problem with columns and Sum Total

Tue, 28 Jan 2014 19:04:11 GMT

you have every thing is good on your side only one presentation setting that i fix in application attached with this text.