topic Re: Average in place of total in pivot table in QlikView

Average in place of total in pivot table

qlikviewwizard — Fri, 20 Nov 2020 18:02:30 GMT

Hi All,

How to get the Average in place of total in pivot table.

Thanks in advance.

Required output:

Re: Average in place of total in pivot table

marcus_sommer — Wed, 26 Aug 2015 14:49:03 GMT

Within the tab presentation in the top left you could set a total-name for each dimension.

- Marcus

Re: Average in place of total in pivot table

Qrishna — Wed, 26 Aug 2015 14:50:43 GMT

Hi Wizard,

Check in the presentation tab.

Re: Average in place of total in pivot table

qlikviewwizard — Wed, 26 Aug 2015 15:10:20 GMT

Hi,

I am using PIVOT table, need Average.

Re: Average in place of total in pivot table

Qrishna — Wed, 26 Aug 2015 15:20:53 GMT

I guess by using dimensionality() function we can achieve this

I guess something like this:

if(dimensionality()=1,RangeMax(0,avg(Slaes)))

but i did not get a chance to implement.

try this and let me know.

Re: Average in place of total in pivot table

qlikviewwizard — Wed, 26 Aug 2015 15:26:04 GMT

Not working.

Re: Average in place of total in pivot table

Qrishna — Wed, 26 Aug 2015 16:10:16 GMT

No Sure if you need this.

See attached.

Re: Average in place of total in pivot table

Wed, 26 Aug 2015 16:34:44 GMT

Hi Qlikviewwizard,

try this

Re: Average in place of total in pivot table

qlikviewwizard — Thu, 27 Aug 2015 15:07:23 GMT

Any solution please.

Re: Average in place of total in pivot table

Qrishna — Thu, 27 Aug 2015 15:21:34 GMT

I have attached the solution yesterday itself. check out

edit:

use the expression :

If(Dimensionality()=2,

round(Sum(Sales)),

round(avg(aggr(sum(Sales),Country,JOB))))

Re: Average in place of total in pivot table

Qrishna — Thu, 27 Aug 2015 15:44:20 GMT

Is that what you were expecting?

Re: Average in place of total in pivot table

jagan — Fri, 28 Aug 2015 01:40:51 GMT

Hi,

Use this expression

If(RowNo() = 0, Sum(Sales)/Count(TOTAL <Country> DISTINCT JOB) , sum(Sales))

Regards,

jagan.

Re: Average in place of total in pivot table

jagan — Fri, 28 Aug 2015 01:43:33 GMT

Hi,

And in Presentation tab Change caption to Average in Labels for Totals text box for both Country and JOB dimension. PFA for solution.

Regards,

Jagan.

Re: Average in place of total in pivot table

qlikviewwizard — Fri, 28 Aug 2015 03:39:12 GMT

Thank you all.