5 Replies Latest reply: Aug 11, 2011 8:37 PM by John Witherspoon

# "Avg" instead of "Sum" at a Pivot Table

Hi all,

I'm still a newbie QVW-Designer,

actualy I try to create a Pivot - Table (attached below).

Due to some previous threads in this forum, I managed to activate the "part sum" in the presentation-area of the properties of the qvw-sheet.

Now my question:

Is it possible to replace this "Sum" by another expression (formula) such as an average.

Thanks for any help in advance

Daniel

• ###### "Avg" instead of "Sum" at a Pivot Table

Hi,

From my idea and knowledge it is only for partial sum not for an average functions. You need to do it manually in expressions.

Regards,

Anand

• ###### Re: "Avg" instead of "Sum" at a Pivot Table

Try This:

Go to Presentation tab:

In Labels for Totals: =Avg()

• ###### Re: "Avg" instead of "Sum" at a Pivot Table

dear rahul,

as you can see (in the attachment) the label box is colored "gray" , so that there's no entry possible.

greetings

daniel

• ###### Re: "Avg" instead of "Sum" at a Pivot Table

Hi!,

U have to write the Code here...

See the Attachment.

• ###### Re: "Avg" instead of "Sum" at a Pivot Table

Your expression can refer to the dimensionality() field to detect whether it's calculating a single line in the chart, or a subtotal, and at what level the subtotal is.  I don't know quite what you want, but maybe something along these lines (you may need a different number than 2):

if(dimensionality()>2,sum(Myfield),avg(MyField))

Another approach is to aggregate in such a way that on the detail lines, it calculates a sum, but the subtotals average those sums.  That won't produce the same results as the above expression, but I'm not sure which results you want anyway.  Something like this:

avg(aggr(sum(MyField),Betrieb,Ort,Monat))

On a detail line, there will only be one number to average, so it will just be the sum.