Problems with AGGR and AVG in a table ( AGGR of Two Dimensions)

[marcel_olmo]

Hello guys, I'm calculating finantial ratios and I have a problem combining AGGR and AVG to calculate the ratio with the hierarchical dimensions year and month.

I show you the problem :

My original set analysis to get the ratio was this :

fabs( ( sum({$<IDCuentaNivel2 = {'51','52','56','58','59'}>} [Asiento Importe]) ) /

sum({$<IDCuentaNivel2 = {'57'}>} [Asiento Importe]) )

And the results were wrong as you can see :

(If you divide 30.45 / 10 (jan to oct), you see that the right result is 3.04, but you get 0.08 ).

And I fixed it with a partial solution, that fixes that only for the "month" dimension, it loses precision with the "year" dimension, and I don't know how to make it right :

avg(aggr( fabs( ( sum({$<IDCuentaNivel2 = {'51','52','56','58','59'}>} [Asiento Importe]) ) /

sum({$<IDCuentaNivel2 = {'57'}>} [Asiento Importe]) ),Año,Mes))

You find the calculation with month dimension fine, but it loses precision with the first dimension of the hierarchical, year.

You see the result of the table with month dimension is fine -> 3.04. But if you divide 11.19 / 6, you get 1.86 instead of 1.83.

How can I fix it?

Many thanks by advance!!

[sathishkumar_go]

Hi

Can u attach ur application here?

-Sathish

[marcel_olmo]

Thank you Sathish for your quick answer.

I have simplified the document to have only the information needed to solve the problem. It's only for confidentiality of our customer.

You get a simple table with the stuff. There is a hierarchical dimension with Year and Month (Año, Mes).

If you open the properties of the table, you'll the important expression called "endeutament" which means "debt" :

And if you click inside the expression, you'll find how I've been calculating before, and how I'm calculating it now.

The point is, if you do the agreggate with 1 dimension (month), it works fine, but if you choose the upper level (year), it gets a closer value, but not the right one.

Any help would be appreciated.

[marcel_olmo]

Please, could somebody answer my question?

A cheesy solution would be do two different tables, doing the AGGR one for year dimension, and the other one for month dimension.

But I hope there's a possible solution with AGGR and 2 dimensions.

[marcel_olmo]

Hey guys. I'm still trying to fix this stuff.

The point is :

if I do the AVG with the AGGR function inside with dimension Year (Año), works fine :

avg(aggr(fabs(sum({$<[Situació Esq N2 Cod-Desc] = {'12-Actiu Circulant'}>} [Asiento Importe]) /

sum({$<[Situació Esq N2 Cod-Desc] = {'23-Passiu Circulant'}>} [Asiento Importe])),Año))

If I do the same with dimension Month (Mes), works fine as well :

avg(aggr(fabs(sum({$<[Situació Esq N2 Cod-Desc] = {'12-Actiu Circulant'}>} [Asiento Importe]) /

sum({$<[Situació Esq N2 Cod-Desc] = {'23-Passiu Circulant'}>} [Asiento Importe])),Mes))

But if I mix it the two dimensions (as I want to do), it doesn't work, it only has precision for Month Dimension, and get a wrong result for Year dimension :

avg(aggr(fabs(sum({$<[Situació Esq N2 Cod-Desc] = {'12-Actiu Circulant'}>} [Asiento Importe]) /

sum({$<[Situació Esq N2 Cod-Desc] = {'23-Passiu Circulant'}>} [Asiento Importe])),Año,Mes))

Please could somebody give a clue to solve that?

Many thanks!!

[marcel_olmo]

I recently tried to fix it doing a nested aggr with the two dimensions, but I couldn't find a way to get my desired result.

I've tried nesting aggr of dimension month, inside the dimension year (what is more logical for me), and it wasn't the solution (it returns the avg of the first year) :

avg(aggr(aggr(fabs(sum({$<[Situació Esq N2 Cod-Desc] = {'12-Actiu Circulant'}>} [Asiento Importe]) /

sum({$<[Situació Esq N2 Cod-Desc] = {'23-Passiu Circulant'}>} [Asiento Importe])),Mes),Año))

I've tried nesting aggr of dimension Year inside the month, and It wasn't the solution as well (it returns the avg of the last two years) :

avg(aggr(aggr(fabs(sum({$<[Situació Esq N2 Cod-Desc] = {'12-Actiu Circulant'}>} [Asiento Importe]) /

sum({$<[Situació Esq N2 Cod-Desc] = {'23-Passiu Circulant'}>} [Asiento Importe])),Año),Mes))

This stuff is turning me crazy, any ideas how can I do to fix this problem?

Many thanks in advance!!

[pover]

Try the following:

avg(aggr( fabs( ( sum({$<IDCuentaNivel2 = {'51','52','56','58','59'}>} [Asiento Importe]) ) /

sum({$<IDCuentaNivel2 = {'57'}>} [Asiento Importe]) ),getcurrentfield(Nombre_Grupo_Jerárquico)))

Saludos.

[marcel_olmo · Accepted solution]

Many thanks Karl for your inspiration.

The point is that getCurrentField of the field group in this case, it doesn't work. I've tried what you told me but it doesn't work.

Instead of that, I tried to put the name of the Hierarchical grup as a name of a field, and now it works perfect.

It does the aggr of the average of the years, months selected.

In my case, the name of the hierarchical group is 'JerTiempo' (is made of Year,Month)

And my correct solution is :

avg(aggr( fabs( ( sum({$<IDCuentaNivel2 = {'51','52','56','58','59'}>} [Asiento Importe]) ) /

sum({$<IDCuentaNivel2 = {'57'}>} [Asiento Importe]) ),JerTiempo))

Sometimes the easiest thing is the better to do.

See you around!!