topic why the final total doesn't work? in QlikView

why the final total doesn't work?

rcandeo — Wed, 26 May 2010 21:48:31 GMT

I have this application attached that has a test with aggregation. This test is to show only values wich amount be bigger than a variable (that is filled with 10.000).

In the line detais it works, but in the final total, the amount didn't considered the aggregation test.

Someone can help me how can I have this final total correct?

why the final total doesn't work?

hector — Wed, 26 May 2010 22:14:03 GMT

Hi, because you have if()'s by every row (changing between the sum() and 0) in the final total, the pivot table shows the Total Expression and not the sum of rows, this behavior cannot be changed in it's properties.

i'll take a look ASAP to help you

rgds

why the final total doesn't work?

Wed, 26 May 2010 22:25:58 GMT

Which fields are you trying to aggregate? I can see you are taking the sum of Valor_Bruto, but what function are the other fields serving? I think the problem is with the way the aggr is being used. If you take out all fields but Valor_Bruto, the calculation continues to function exactly like it is now.

why the final total doesn't work?

johnw — Thu, 27 May 2010 01:11:07 GMT

If you want the "sum of rows" in a pivot table, you have to do it by sum(aggr(expression,dimensions)). So this would be your Valor Bruto expression:

sum(aggr(if(sum(aggr(Valor_Bruto,Filial,Documento,Codigo_Fornecedor
,Nota,Valor_Pagamento,Chave_Pagamento))>Valor_Minimo
,sum(aggr(Valor_Bruto,Filial,Documento,Codigo_Fornecedor
,Nota,Valor_Pagamento,Chave_Pagamento)),0)
,UM_NF,Fornecedor,Codigo_Fornecedor,CNPJ_CPF,BancoAgencia
,CodigoBancoAgencia,Item_Descricao1))

Seems to work.

why the final total doesn't work?

rcandeo — Thu, 27 May 2010 12:47:41 GMT

John, if i put in the aggregation all the fields that are in the row, it doesn't work. My aggregation is already working, only the final totals that are wrong, and mabye cause i'm putting if in the beggining like RGDS said.

why the final total doesn't work?

rcandeo — Thu, 27 May 2010 12:53:31 GMT

Aaron, If I does't make an aggregation, it shows me no value cause it's a consolidated table and I'm making a comparison of the sum(Valor_Bruto) to show only if it's bigger than the variable Valor_Minimo.

why the final total doesn't work?

rcandeo — Thu, 27 May 2010 13:17:52 GMT

I´ve tried to adjust but it is giving me an error. See if you help me please?

sum(if(sum(aggr(Valor_Bruto,Filial,Documento,Codigo_Fornecedor,Nota,Valor_Pagamento,Chave_Pagamento))>Valor_Minimo,aggr(Valor_Bruto,Filial,Documento,Codigo_Fornecedor,Nota,Valor_Pagamento,Chave_Pagamento),0))

The error seems to be in the bold part of the expression...

why the final total doesn't work?

Thu, 27 May 2010 15:24:03 GMT

I don't understand what you are trying to do with "aggr(Valor_Bruto, Filial, Documento...)", but I think that is part of your problem. If your comparison is to be made against only the sum of Valor_Bruto, you would need something a bit more like this:

Sum(aggr(If(Sum(Valor_Bruto)>ValorMinimo, Sum(ValorBruto)), UM_NF,Fornecedor,Codigo_Fornecedor,CNPJ_CPF,BancoAgencia ,CodigoBancoAgencia,Item_Descricao1))

why the final total doesn't work?

rcandeo — Thu, 27 May 2010 15:55:10 GMT

Aaron, it didn't work cause if I compare only the amount of Valor_Bruto without aggregation, the sum will result in 0 value.

That's why I'm using the aggregation.

Try to put a new expression beside my expression of Valor Bruto and you will see.

why the final total doesn't work?

johnw — Thu, 27 May 2010 18:01:17 GMT

[quote user="Robson Candêo"]John, if i put in the aggregation all the fields that are in the row, it doesn't work. My aggregation is already working, only the final totals that are wrong, and mabye cause i'm putting if in the beggining like RGDS said.

It doesn't work? Did you TRY cutting and pasting what I gave you? Because I tried it. It didn't change ANY of your individual row numbers, only the final total, which became the sum of your rows. For the final total, it gave me this number:

2.793.139,05

That is the exact same number that Excel tells me is the sum of the values in the column. That's what I thought you wanted. If that is NOT the number you want, what number DO you want? And why?

And yes, your expression doesn't work on the total line because of the way the if(sum()>...,sum()) behaves in a pivot table, or by default in a straight table. The sums and the if() apply to everything, not to one row at a time. The sum of everything is higher than your minimum. So it shows you the total of everything. You needed, if I understand correctly, the sum of rows. What I posted is a way to get a sum of rows in a pivot table.

why the final total doesn't work?

rcandeo — Thu, 27 May 2010 20:16:34 GMT

Sorry John, you were right. It worked and I simplified the expression a little bit:

sum(aggr(if(sum(aggr(Valor_Bruto,Filial,Codigo_Fornecedor,Nota))>Valor_Minimo

,sum(aggr(Valor_Bruto,Filial,Codigo_Fornecedor,Nota)),0)

,UM_NF,Fornecedor,Codigo_Fornecedor,CNPJ_CPF,BancoAgencia

,CodigoBancoAgencia,ContaBancaria,Item_Descricao1))

Can you help me doing the same thing in the chart "Detalhes Bancos - Relatório de Madeiras"?

I' ve tried this expression but it didn't worked. The idea is to show an amount only when the aggregation is bigger than the variable Valor_Minimo, but when printing a value, it must be detailed as using sum(Valor_Bruto)

sum(aggr(if(sum(aggr(Valor_Bruto,Filial,Codigo_Fornecedor,Nota))>Valor_Minimo

,sum(aggr(Valor_Bruto,Filial,Codigo_Fornecedor,Nota)),0)

,UM_NF,Fornecedor,Codigo_Fornecedor,CNPJ_CPF,BancoAgencia

,CodigoBancoAgencia,ContaBancaria,Item_Descricao1))

why the final total doesn't work?

johnw — Sat, 29 May 2010 00:36:57 GMT

I'm very unclear what you want, but maybe you're asking for this?

if(dimensionality()<3
,sum(Valor_Bruto)
,your original "Valor Bruto" expression here)

why the final total doesn't work?

rcandeo — Sat, 29 May 2010 04:04:33 GMT

Thank you John for continue trying to help me.

Unfortunately it's not that.

For the line totals it works, but I need to show in the detail lines the sum of individual Valor_Bruto, but only when the aggregation of the lines that will result in the next line total is bigger than the variable Valor_Minimo.

I've made me clear now please?

why the final total doesn't work?

amien — Sun, 13 Feb 2011 21:12:17 GMT

"If you want the "sum of rows" in a pivot table, you have to do it by sum(aggr(expression,dimensions))."

Thanks for that John .. BTW new picture? 🙂

why the final total doesn't work?

johnw — Mon, 14 Feb 2011 19:21:57 GMT


Amien wrote:BTW new picture? 🙂

Yep. The previous picture was about a year and a half old, so I figured it was time for a new one.