topic Re: Average instead of sum in pivot table in QlikView

Average instead of sum in pivot table

Thu, 11 Oct 2012 11:49:12 GMT

Hi!

I've created a pivot table with som expressions which are represented as columns. Is it possible to show an avarage at the bottom of a columns instead of a partial sum?

Re: Average instead of sum in pivot table

Gysbert_Wassenaar — Thu, 11 Oct 2012 12:18:47 GMT

On the expression tab of the properties screen change the Total Mode to Average of Rows. You can select Average in the combobox after you check the third radio button.

Re: Average instead of sum in pivot table

Thu, 11 Oct 2012 12:23:31 GMT

Total mode to Average is greyed I'm afraid...

Re: Average instead of sum in pivot table

johnw — Thu, 11 Oct 2012 19:57:38 GMT

Pivot tables don't let you set the total mode unless it's something new in a more recent version (we're on v10). You can do it by changing your column expressions like this:

avg(aggr(current column expression,dimension1,dimension2,...))

See the attached example.

Re: Average instead of sum in pivot table

Tue, 20 Aug 2013 13:44:06 GMT

Works beautifully.

I know I'm getting greedy here, but any way to add the subtotal as well ? (i.e. have two columns on the right, one for avg and another for tot).

Thanks!

Re: Average instead of sum in pivot table

Tue, 17 Jun 2014 21:01:26 GMT

Sorry to resurrect a super old thread here but this illustrates a very specific problem I'm having. Can you *please* tell me why the number 49.13 should not be 52.39?

Why is the average of (78.50 + 38.00 + 40.67) / 3 *NOT* 52.39?

Am I reading this wrong? I'm having the exact same "problem" with some partial totals in a pivot I'm working on....the bottom line partials aren't averaging properly, and this example is behaving the same way. *Unless* I'm not reading how these should display properly?

Thanks SO much for your clarification!

Re: Average instead of sum in pivot table

-SW- — Mon, 15 Jun 2015 14:09:34 GMT

Hi Derek,
In Johns solution, you cant really use the average of the subtotals for your final total. You should add up all the individual lines, and divide by the total number.

i.e. (72,85,30,23,61,7,31,84) / 8 = 49.125

Re: Average instead of sum in pivot table

Wed, 01 Jul 2015 18:53:34 GMT

Hi Josh,

I'd like to know if there is a solution for when you want to display your dimmensions as columns and your expressions as rows, i.e., showing the average of each of your expressions in a column.

Thank you very much,

Re: Average instead of sum in pivot table

svenkita — Thu, 02 Jul 2015 06:00:11 GMT

Attached is a solution for having the totals in pivots as averages

I have used the Dimensionality() to do this

If(Dimensionality()=1,Avg(SALES), Sum(SALES))

Re: Average instead of sum in pivot table

pratap6699 — Thu, 02 Jul 2015 07:55:14 GMT

what is dimensionality(),,,,,,will you explain something about that

Re: Average instead of sum in pivot table

svenkita — Thu, 02 Jul 2015 10:17:57 GMT

Dimensionality() indicates the level of subtotals in pivot tables. For example,

if you have a pivot table with dimensions A, B, C and D,

subtotals for A will have a dimensionality of 1

subtotals for B will have a dimensionality of 2

subtotals for C will have a dimensionality of 3

subtotals for D will have a dimensionality of 4

How to use - Dimensionality()