Need help with AGGR in a dimension

[#1]

Hi guys! I have a calcuated dimension like:

Aggr(Sum(Balance1), Column1, Column2, Column3, Column4)

Which works great but its only part of what I need. I have a second balance column (Balance2) that I need to use to divide Balance 1 by, BUT only if certain values appear in Column5 and Column6. So I guess something like

Aggr(Sum(Balance1), Column1, Column2, Column3, Column4) / Aggr(Sum(Balance1), Column1, Column2, Column3, Column4, Column5, Column6) but I need to be able to be sure the values of 'X' is in Column5, and the value of 'Y' is in Column6. Is there a way to do this? I feel like i am halfway there....

Thanks

[jolivares]

Post a sample of your data and the results that you are expecting...

[nagaiank · Accepted solution]

You may try the following:

Aggr(Sum(Balance1), Column1, Column2, Column3, Column4)

/

Aggr(Sum({<Column5={"X"}, Column6={"Y"}>} Balance1), Column1, Column2, Column3, Column4)

[#4]

Actually, I thought this was working but it doesnt appear to be as when I manually sum up the values, it doesnt match what this returns...

[lft]

Hi David,

Do you need to divide the resulting average or to divide each sum individually and average the ratios ?

in the first case, the expression given by NagaianK should work (except that in my understanding it is Balance2 at the bottom).

Aggr(Sum(Balance1), Column1, Column2, Column3, Column4)

/

Aggr(Sum({<Column5={"X"}, Column6={"Y"}>} Balance2), Column1, Column2, Column3, Column4)

in the second case, the expression should be

Aggr(

     Sum(Balance1)

     /

     Sum({<Column5={"X"}, Column6={"Y"}>} Balance2)

     , Column1, Column2, Column3, Column4

)

I guess you have tested that the Sum({<Column5={"X"}, Column6={"Y"}>} Balance2) is never 0 (this is basic maths). If one of the values = 0, the the division will return Null(). It will thus be ignored by the average agregation function.

if this does not work, can you tell me : is your expression in a chart of some kind ?

if so, don't forget to also include the chart dimensions as parameters of the Aggr :

Aggr(Expr, Column1, Column2, Column3, Column4, Dim1, Dim2)

Loic

[#6]

Yeah I see it was my mistake this does work!

Question though, when it does return null, is there a way to set the result to 0? Like ISNULL is SQL Server?

[lft]

you can either test if the bottom = 0, and if so return 0, and if not return the division

or you can test if the division is null with the IsNull function. There is no IsNull function as in SQL which accepts a replacement value as a second parameter. Our IsNull function only accept one parameter (the expression) and returns a boolean.

You will have to wrap your test in an if statement.

Loic