Problem with columns and Sum Total

[pgalvezt]

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.

[jerem1234]

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!

[Colin-Albert]

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

[Anonymous]

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

[pgalvezt]

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

[Anonymous]

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.

[pgalvezt]

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.

[Anonymous]

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.

[pgalvezt]

Sorry wrong file.

I tried your formula but gave me an error.

[Anonymous · Accepted solution]

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