5 Replies Latest reply: Jun 4, 2012 4:18 PM by Jose Tos

# weighted averages with aggr

Hi, I´m trying to use aggr function to calculate weighted averages.

I have data like this:

 I/D LINEA CENTRO TIPO     Promedio AñoPeriodo nPeriodo Mes %Fecha

 DIRECTO LECHE 700042 Efectivo 0.61 2010 1 Ago 31/08/2010 DIRECTO LECHE 700042 Efectivo 0.74 2010 1 Ago 31/08/2010 DIRECTO LECHE 700042 Zafral 0.74 2010 1 Ago 31/08/2010 DIRECTO LECHE 700042 Efectivo 0.77 2010 1 Ago 31/08/2010 DIRECTO LECHE 700042 Efectivo 0.81 2010 1 Ago 31/08/2010 DIRECTO LECHE 700042 Efectivo 0.8 2010 2 Sep 30/09/2010 DIRECTO LECHE 700042 Efectivo 0.87 2010 2 Sep 30/09/2010 DIRECTO LECHE 700042 Zafral 0.87 2010 2 Sep 30/09/2010 DIRECTO LECHE 700042 Efectivo 0.68 2010 3 Oct 31/10/2010 DIRECTO LECHE 700042 Efectivo 0.74 2010 3 Oct 31/10/2010 DIRECTO LECHE 700042 Efectivo 0.77 2010 3 Oct 31/10/2010 DIRECTO LECHE 700042 Efectivo 0.81 2010 3 Oct 31/10/2010 DIRECTO LECHE 700042 Efectivo 0.84 2010 3 Oct 31/10/2010 DIRECTO LECHE 700042 Zafral 0.84 2010 3 Oct 31/10/2010

 Date Days 31/08/2010 31 30/09/2010 30 31/10/2010 31

This is a particular case with a few months, so what I need is to add the averages (field Promedio) of the month multiplied by the days of the month for each month since Agoust to October and divide that result by the total days of those months

 Avg Month Days Month Avg Month*Days Month Total Avgs Total Days weighted average Ago 3,67 31 113,77 335,05 92 3,64 Sep 2,54 30 76,2 Oct 4,68 31 145,08

This is my first aggr function and I have a doubt apart of all this, in the aggr function, the dimensions would be like a group by??

Well, this is my function and it´s not working at all.

sum(aggr(sum({<Periodo={'<=\$(=Periodo)'},AñoPeriodo={'=\$(=AñoPeriodo)'},PERIODO=>}Promedio)

,%Fecha,CENTRO,TIPO,nPeriodo) * DaysMonth)

/

sum({<Periodo={'<=\$(=Periodo)'},AñoEjercicio={'=\$(=AñoEjercicio)'},PERIODO=>}DaysMonth)

Regards,

Chema.

• ###### weighted averages with aggr

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

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

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

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

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.