topic Re: Calculate Average Over All Dimension Values in Pivot Table in QlikView

Calculate Average Over All Dimension Values in Pivot Table

mpressey — Wed, 25 Nov 2020 16:16:04 GMT

In the attached example, how can I calculate the average over all weekdays versus the average over just days with sums?

Re: Calculate Average Over All Dimension Values in Pivot Table

sunny_talwar — Fri, 02 Feb 2018 15:22:28 GMT

What is your expected output for the two numbers and which object do you need this in?

Re: Calculate Average Over All Dimension Values in Pivot Table

Anil_Babu_Samineni — Fri, 02 Feb 2018 15:22:48 GMT

Is this you want to see?

avg(aggr(sum(Visit),Hour,WeekDay,Location))

Re: Calculate Average Over All Dimension Values in Pivot Table

mpressey — Fri, 02 Feb 2018 15:44:27 GMT

In the avg pivot, I would like the average calculation to treat the nulls, '-', as zero values.

Re: Calculate Average Over All Dimension Values in Pivot Table

sunny_talwar — Fri, 02 Feb 2018 15:53:54 GMT

Try this

If(Dimensionality() = 0,

Sum(Aggr(Avg(Aggr(Sum(Visit),Hour,WeekDay,Location))/Avg(Aggr(Count(DISTINCT Visit),Hour,WeekDay,Location)),Hour,WeekDay,Location))/Count(DISTINCT TOTAL WeekDay),

Avg(Aggr(Sum(Visit),Hour,WeekDay,Location))/Avg(Aggr(Count(DISTINCT Visit),Hour,WeekDay,Location)))

Re: Calculate Average Over All Dimension Values in Pivot Table

sunny_talwar — Fri, 02 Feb 2018 15:59:33 GMT

Simplified version of the expression

If(Dimensionality() = 0,

Sum(Aggr(Avg(Visit),Hour,WeekDay,Location))/Count(DISTINCT TOTAL WeekDay),

Avg(Visit))

Re: Calculate Average Over All Dimension Values in Pivot Table

mpressey — Fri, 02 Feb 2018 16:07:59 GMT

Thank you Sunny. This is very helpful. I have not used dimensionality very much.