why the final total doesn't work?

[rcandeo]

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?

[hector]

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

[#3]

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.

[johnw]

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.

[rcandeo]

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.

[rcandeo]

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.

[rcandeo]

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

[#8]

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

[rcandeo]

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.

[johnw]

[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.