Multiple Totals on Dataset

[Anonymous]

I have a PivotTable Chart with dimension Year, Month, Fund, Sector and expression of AvgCapLong and AvgCapShort. I want to show partial sums at the Year, Month, Fund and Sector level.

Currently the Expression for AvgCapLong is simply SUM(AvgCapLong). I calculate the AvgCap in the database, hence it's SUM rather than Avg.

I want to see the following partial sums:

At the Sector level (dimensionality=4) : SUM(AvgCapLong) - This works fine.

At the Fund level (dimensionality=3): SUM(AvgCapLong) - This works fine also. This is the Sum of average across sectors.

At the Month Level (dimensionality=2): SUM(AvgCapLong) - Perfect. This is the Sum across Funds.

At the Year level (dimensionality=0): SUM(AvgCapLong). Problem. I want the AVERAGE of the Monthly Sum of AvgCapLong. The Average should be the average of monthly sum(avgCapLong). How do I put this in the expression?

I tried - but it didn't like the expression.

IF(Dimensionality()=0, avg( {$<Dimensionality()=2}> } SUM(ACR_AvgCap_DALong))/1000000, Sum(ACR_AvgCap_DALong)/1000000)

[jerem1234]

You'll need to use the aggr function like:

IF(Dimensionality()=0,

avg(aggr(sum(ACR_AvgCap_DALong), Month))/1000000,

Sum(ACR_AvgCap_DALong)/1000000)

Aggr will aggregate based on your field Month. So it will sum ACR for each month, then the avg will take the average of all those sums.

Hope this helps!

[Anonymous]

Jerem1234,

Perfect....that works wonderfully. Thanks