4 Replies Latest reply: Jan 24, 2012 8:56 AM by Lee Alderdice RSS

    Get the Average of a Set

      Hi All,

       

      From a set of raw data I am creating a sum of Amount, grouped by Partner and Type in a pivot table. 

       

      I need to get the average of those sum values but in a separate table and grouped by a new dimension, Band and then Type as before.

       

      So I am creating a set as below -

       

      =sum          ({$<Type>}                     (aggr ({$<Type>}Amount), Partner))

       

      What I can't work out is how to find the average of that set of values.

       

      I hope that makes sense.

       

      Thanks

       

      Lee

        • Get the Average of a Set
          Stefan Wühl

          Lee,

           

          could you post some lines of sample data, maybe with an Inline table together with your expected results? Or a small sample app?

           

          In your above expression, I am not sure what you want to achieve with {$<Type>} ? Maybe you want to use the total qualifier with dimensions, like total<Type> ?

           

          Again, I think I have not fully understood your requirements, so some sample data and expected results would really help me.

           

          Regards,

          Stefan

            • Get the Average of a Set

              Hi,

               

              I have uploaded a sample of the document.  On the front page you will see a table called Scorecard POC.

               

              Above that is another table called summary.  The summary table needs to total the Scorecard column values and average it over the Scorecard data.

               

              If you average it over the raw data, the number is not as required.  So what I was trying to do was create a dataset mirroring the Scorecard values by type and avg them out over the distinct partner count.

               

              Hope that helps.

               

              Lee