4 Replies Latest reply: May 12, 2011 6:30 AM by Erika Jain RSS

    Averages

      Hi,

       

      I have a Pivot table used to display the number of hours an Employee works over a number of weeks. This is in the format of:

                                                   Week |           1      2      3      4      5 ...

      Employee Employee Name                    10     20    10    10    10

      The dimensions are: Employee ID, Name, Week_no

       

      The expression is a sum of hours that count towards actual working hours: sum(total_hours_attended)

      What I would like to be able to do, is to select a number of weeks (in this case 5) and at the end of the row, provide an overall average, e.g:

                                                    Week |          1     2     3     4     5     Average

      EmployeeEmployee Name                      10   20   10   10   10        12

       

      If I create another expression to provide an average, it will average each week. Is there a way to have the average calculate at the end of the line?

       

      Thanks in advance

      Ben     

        • Averages

          Hi,

           

          A way to do that is :

           

          if( columnNo() = 0 , sum(total_hours_attended)/count(distinct week) ,  sum(total_hours_attended) )

           

          Benoît

          • Averages
            Dennis Hoogenboom

            Hi ben,

             

            I know one way to do this ,  but only if the hours in your expression is not a Sum().

             

            If your expression is just [Hours] you can change this to AVG[Hours] , this will return the average on every line but this will be the same if you don't use a calculation in your expression (fe: AVG(10) = 10)

            So this way you have the right hours in your expresion.

            Now go to the tab Presentation and now check "Show Partial Sums" for Employee Name.

             

            Hope this works for you.

            • Re: Averages
              Patrick Laredo

              hi,

               

              if you use as expression

               

              [Code]

              sum(hours)/count(distinct Week)

              [/Code]

               

              and on the presentation tab you tick "show partial sums" for Week and change the "Label for Totals" to "avg" for the same

               

              you will get the following

               

              avg.png

              • Re: Averages

                Hi,

                I have used Bcuisiniere's code and implemented what you wanted.