# QlikView Layout & Visualizations

Discussion Board for collaboration on QlikView Layout & Visualizations.

Not applicable

## Can you do a different aggregate calculation in the total of a pivot table .

Can you do a  different aggregate calculation in the total  of a pivot table .

I have a pivot table that has a calculation as the measure

I would like to have the total as an avg of the rows

5 Replies
Valued Contributor

## Re: Can you do a different aggregate calculation in the total of a pivot table .

=if( Dimensionality() = 1

, sum(Qty)

, avg(Qty)

)

## Re: Can you do a different aggregate calculation in the total of a pivot table .

You can use the dimensionality() function. Something like if(dimensionality()=0, avg(aggr(sum(Sales),Product)),sum(Sales)) in a pivot table that has Product as dimension and Sales as a measure for the calculations.

talk is cheap, supply exceeds demand
Valued Contributor

## Re: Can you do a different aggregate calculation in the total of a pivot table .

Hi Oliver,

Since the pivot table doesn't have the option to change the Total mode of the expressions in the properties dialog like the straight table, you will have to do that in your expressions. If it's just a simple average of the calculations you can achieve that using the AGGR function (advanced aggregation). For example:

avg(aggr(sum(Value),Dimension))

Cesar

Not applicable

## Re: Can you do a  different aggregate calculation in the total  of a pivot table .

my calc is a little different

=if (RegMonth =ActivityPeriod30,200,sum(Rev))

the resulting sum  will always be the else condition .

Should the if condition be in  set analysis . How would the calculation be written?

Not applicable

## Re: Can you do a  different aggregate calculation in the total  of a pivot table .

select COUNT (*) ,MONTH(c.created_at)

from tbStageF_content  (nolock) c

where

creator_source=100

and  year(c.created_at) =2013

and MONTH(c.created_at) >= 1

group by

MONTH(c.created_at)

order by 2