Get the Average of a Set

[#1]

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

[swuehl]

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

[#3]

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

[swuehl · Accepted solution]

In summary table, I think you'll get the totals of POC in each line using

=Sum({<Legitimate ={1}>} total<Type>  Amount)

so the average could be

=Sum({<Legitimate ={1}>} total<Type>  Amount) /count(DISTINCT Partner)

Is this what you are looking for?

[#5]

Thank you so much, that is perfect