
weighted averages with aggr
Oleg Troyansky Jun 4, 2012 1:01 PM (in response to Jose Tos)I'm assuming that the two tables that you described, are associated by Date. If they are not linked, the following won't work correctly. You have to make sure that the two tables are linked.
Then, try including the multiplication by DaysMonth inside of your AGGR calculation:
sum(aggr(sum({<Periodo={'<=$(=Periodo)'},AñoPeriodo={'=$(=AñoPeriodo)'},PERIODO=>}
Promedio* DaysMonth)
,%Fecha,CENTRO,TIPO,nPeriodo) )
/
sum({<Periodo={'<=$(=Periodo)'},AñoEjercicio={'=$(=AñoEjercicio)'},PERIODO=>}DaysMonth)

weighted averages with aggr
Jose Tos Jun 4, 2012 1:20 PM (in response to Oleg Troyansky )You are assuming well, they are linked by Date.
I´ve tried what you said but I`m getting incorrect results.
thank´s

weighted averages with aggr
Oleg Troyansky Jun 4, 2012 1:28 PM (in response to Oleg Troyansky )I'm not sure why you have different Set Analysis condition in the numerator and in the denominator  usually the conditions should be the same for the weighted average logic to work correctly.
If you post an example, we could try and and find the problem, otherwise it's very hard to guess...

weighted averages with aggr
Jose Tos Jun 4, 2012 2:14 PM (in response to Oleg Troyansky )I know but I can´t upload it right now. The behaviour is as follows:
User selects a Period, imagine that is October 2010, so because my fiscal year starts in August, I need to make calculations between those months in the same year, that´s the reason of my conditions in numerator and denominator: {<Periodo={'<=$(=Periodo)'},AñoPeriodo={'=$(=AñoPeriodo)'},PERIODO=>} //Periodo or Month is the same because they are linked in my calendar so Perdiodo =1 is Month = August
%Fecha,CENTRO,TIPO,nPeriodo //with these dimensions I want to add the averages in the numerator by month but each month multiplied by days of month:
Avg August * 31 + Avg September * 30 + Avg October *31
and the denominator must be 31+30+31, total days of the three months.
If the syntax of my expression is correct, I need to pay more attention on the relationships of my tables but I don´t see anything strange...
May be I need to calculate something in the load, I don´t know wich is the best way to resolve it.
Thank you anyway.

weighted averages with aggr
Jose Tos Jun 4, 2012 4:18 PM (in response to Jose Tos)Finally, I resolved it without aggr function but I´ve learned about that, so I´m happy!!
If anyone is interested, the best way I found is creating other table in the load script with partial sums for each month like: sum(Promedio)*DaysMonth grouping by Month.
So we have in another table the Month or Date with DaysMonth.
Finally, this would be the expression where we add all month averages until the month selected and depending on the dimensionality, we calculate the weighted average dividing by Total Days until the month selected or we calculate the simple average dividing by the number of months until the month selected.
if(Dimensionality()=5,
sum({<Period={'<=$(=Period)'},FiscalYear={'=$(=FiscalYear)'},PERIOD=>}[Avg Month])
/
sum({<Period={'<=$(=Period)'},FiscalYear={'=$(=FiscalYear)'},PERIOD=>}DaysMonth)
,sum({<Period={'<=$(=Period)'},FiscalYear={'=$(=FiscalYear)'},PERIOD=>}Avg)
/
nPeriod)
Regards.


