Problem with AGGR()

[jukah_do]

Hi,

I have a straight table with the following columns:

Dimension: Period
Measure 1: SUM({<Period=E(Period)>}Y)
Measure 2: LINEST_M(TOTAL Y, X)*(SUM({<Period=E(Period)>}X))+LINEST_B(TOTAL Y, X)
Measure 3: FABS(SUM({<Period=E(Period)>}Y)-(LINEST_M(TOTAL Y, X)*(SUM({<Period=E(Period)>}X))+LINEST_B(TOTAL Y, X)))

Everything works perfectly. When I turn Totals, I get an average for Measure 3. I need to also have it as a separate KPI object, but the following measure does not return a number, just '-':

AVG(
AGGR(
FABS(
SUM({<Period=E(Period)>}Y)
-
(LINEST_M(TOTAL Y, X)*(SUM({<Period=E(Period)>}X))+LINEST_B(TOTAL Y, X))),
Period)
)

Any idea what is wrong here?

Thanks!

[jukah_do]

Anyone with a suggestion, please?

I have been trying to make this work for a while now, all formulas work in a straight table, but I cannot get the average as KPI. 😖

[javiersassen]

never worked with these functions before, but think I figured it out. I'll show you what I think is the solution. Let me know if you want to know how/why it works like this, but best thing (for me in particular) would be to just accept it works like this haha!

make a variable let's say vMeasure2 that's equal to: 

=LINEST_M(TOTAL Y, X)*(SUM(Distinct {<Period=E(Period)>}X))+LINEST_B(TOTAL Y, X)

then next, in your KPI object use the following formula:

IF(GetSelectedCount(Period) = 0,Fabs(vMeasure2),AVG({<Period=E(Period)>} Aggr(Fabs(Sum({1} Y) - vMeasure2),Period)))

Hope this helps you out! Feel free to contact me;)

[jukah_do]

Thanks a lot, you pointed me in the right direction. 🙂 Here what worked:

AVG({<Period=E(Period)>}AGGR(FABS(SUM({<Period=E(Period)>}Y)-(LINEST_M(TOTAL Y, X)*SUM({<Period=E(Period)>}X)+LINEST_B(TOTAL Y, X))),Period))