Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
You may try the following:
Aggr(Sum(Balance1), Column1, Column2, Column3, Column4)
/
Aggr(Sum({<Column5={"X"}, Column6={"Y"}>} Balance1), Column1, Column2, Column3, Column4)
Post a sample of your data and the results that you are expecting...
You may try the following:
Aggr(Sum(Balance1), Column1, Column2, Column3, Column4)
/
Aggr(Sum({<Column5={"X"}, Column6={"Y"}>} Balance1), Column1, Column2, Column3, Column4)
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...
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
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?
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