Multiple Totals on Dataset

Anonymous — Mon, 20 Jan 2014 16:07:05 GMT

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)

Re: Multiple Totals on Dataset

jerem1234 — Mon, 20 Jan 2014 16:24:12 GMT

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!

Re: Multiple Totals on Dataset

Anonymous — Mon, 20 Jan 2014 17:30:47 GMT

Jerem1234,

Perfect....that works wonderfully. Thanks

topic Re: Multiple Totals on Dataset in QlikView

Multiple Totals on Dataset

Re: Multiple Totals on Dataset

Re: Multiple Totals on Dataset