4 Replies Latest reply: Apr 25, 2012 9:40 AM by marthein RSS

    Averages Per Dimension

      Hi all,

       

      please find attached an example of averages I'm trying to calculate.

       

      As you can see, I have two different shipments with different number of invoices.

       

      In the table "Leadtime A-B", I'd like to know the average leadtime of all shipments

      1. Average of the lead time between A and B per shipment

      E.g. avg leadtime A-B for shipment A =(2+3+5+1+1)/5=12/5

      E.g. avg leadtime A-B for shipment B=(1+2)/2=3/2

       

      2. Average of these leadtimes in total

      avgleadtime A + avgleadtime B / total number of shipments = ((12/5)+(3/2))/2

       

      If more shipments are selected, then this should be calculated automatically.

       

      I'd like to have it in an expression and over the current selections.

       

      Thanks a lot for your help!

       

      Kind regards,

       

      Marthein

        • Re: Averages Per Dimension
          jagan mohan rao appala

          Hi,

           

          Hope attached file helps you.

           

          Regards,

          Jagan.

            • Re: Averages Per Dimension

              Hi Jagan,

               

              thanks for the quick reply!

               

              I see on total you have an average of 2.43 while I would expect an average of 2.45 (an average of 2.4 and 2.5). This 2.45 is exactly the number I'm after. Can you help me with it?

               

              Thanks,

               

              Marthein

                • Re: Averages Per Dimension
                  jagan mohan rao appala

                  Hi,

                   

                  I think it is not possible, you cannot modify row total, you can do total by expression or sum of rows in straight table.

                   

                  When you sum Total by expression, it do by  (17)/2  This is one option.

                   

                  The other option is Sum of rows, it just sums up the values.  (2.4 + 2.5)

                   

                  Hope you understand.

                   

                  Regards,

                  Jagan.

                    • Re: Averages Per Dimension

                      Hi,

                       

                      please find attached another file which shows the issue I'm dealing with.

                       

                      Basically my source consists of invoices related to shipments, and each shipment has a time "registration" (time A and time B) which is registred at the "invoice" level.

                       

                      If you look at the file, you see I have an average leadtime (based on the formula provided by you) of 5.43, while the average leadtime per shipment would be 5 (leadtime A + leadtime B divided by 2).

                       

                      The problem is caused by the fact that shipment A has more invoices, and thus will give more weight to the average equation, while it "should" be independent of the number of invoices in each shipment.

                       

                      Thanks for your help,

                       

                      Marthein