Function AGGR in Set Analisys(pivot table)

[#1]

Hi, someone help me please!

I have probem with the function AGGR because, when I put only one dimension, the value is different that when I put others dimensions in the sum. For example:

When I put: sum(aggr(if(sum({<ANO={$(=YEAR(DATA_INI))}>} valor )<>0 and (sum( {<ANO={$(=YEAR(DATA_INI_ANT))}>} valor )<>0),sum({<ANO={$(=YEAR(DATA_INI))}>} valor ),0),filial))

That's work and I can to see the value of the filial, and the sum is correct.

But, when  I try to put something like that: sum(aggr(if(sum({<ANO={$(=YEAR(DATA_INI))}>} valor )<>0 and (sum( {<ANO={$(=YEAR(DATA_INI_ANT))}>} valor )<>0),sum({<ANO={$(=YEAR(DATA_INI))}>} valor ),0),filial,otherdimension))

The value is different. I understood why that happens but I need to solve this problem. Probably the solution is make the same but without AGGR, I tried a lot, but nothing.

Ps: Sorry because my english is horrible.

[sunny_talwar]

What is the logic behind adding otherdimension in your Aggr? I am not sure I understand why you want to add it when you getting the result without it?

[swuehl · Accepted solution]

You probably know that this kind of expression is used to express the sum-of-rows in a pivot table.

If your sum-of-rows is different from the expression total (e.g. because you may have records linking multiple times into different segments of your table), then adding dimensions will make a difference.

But maybe you can add dimensions, still achieving the same using:

sum(

aggr(

if(sum({<ANO={$(=YEAR(DATA_INI))}>} TOTAL<filial> valor )<>0 and (sum( {<ANO={$(=YEAR(DATA_INI_ANT))}>} TOTAL<filial> valor )<>0),sum({<ANO={$(=YEAR(DATA_INI))}>} TOTAL<filial> valor ),0)

,filial,otherdimension)

)

[#4]

Sunny, when you make a Pivot table, if you intend to see others levels(for example, you need to see a car, and all colors available) , you need to use Aggr function. The problem is when you use SubTotal, because when you have a lot of dimensios, the subtotal don't work. It's difficult to explain, maibe swuehl can to explain better than me.

[#5]

Swehl, thats work! Thank you for your attention!