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

    weighted averages with aggr

    Jose Tos

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

       

      I have data like this:

       

      I/DLINEACENTROTIPO     Promedio AñoPeriodo nPeriodo Mes %Fecha

       

      DIRECTOLECHE700042Efectivo0.6120101Ago31/08/2010
      DIRECTOLECHE700042Efectivo0.7420101Ago31/08/2010
      DIRECTOLECHE700042Zafral0.7420101Ago31/08/2010
      DIRECTOLECHE700042Efectivo0.7720101Ago31/08/2010
      DIRECTOLECHE700042Efectivo0.8120101Ago31/08/2010
      DIRECTOLECHE700042Efectivo0.820102Sep30/09/2010
      DIRECTOLECHE700042Efectivo0.8720102Sep30/09/2010
      DIRECTOLECHE700042Zafral0.8720102Sep30/09/2010
      DIRECTOLECHE700042Efectivo0.6820103Oct31/10/2010
      DIRECTOLECHE700042Efectivo0.7420103Oct31/10/2010
      DIRECTOLECHE700042Efectivo0.7720103Oct31/10/2010
      DIRECTOLECHE700042Efectivo0.8120103Oct31/10/2010
      DIRECTOLECHE700042Efectivo0.8420103Oct31/10/2010
      DIRECTOLECHE700042Zafral0.8420103Oct31/10/2010

       

       

      DateDays
      31/08/201031
      30/09/201030
      31/10/201031

       

       

      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 MonthDays Month    Avg Month*Days MonthTotal AvgsTotal Daysweighted average
      Ago3,6731113,77335,05923,64
      Sep2,543076,2

      Oct4,6831145,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
          Oleg Troyansky

          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

              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

                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

                    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

                        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.