topic Pivot Table (weighted Avg) issue. in QlikView

Pivot Table (weighted Avg) issue.

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

Hello All,

I am trying to create a pivot table in QlikView and perform a weighted average calculation, I have 3 dimensions(AREA, AREA 2, PRODUCT CODE) while two metric fields (RATE and Factor), I am trying to get the value in F14 cell (green colored ) which is basically derived by sum(Rate*Factor)/ sum(Rate). I tried using Aggr and avg function but somehow not able to get the right value.

Attached is the sample data set I used for the below pivot. Thanks for the help !

Re: Pivot Table (weighted Avg) issue.

Anil_Babu_Samineni — Tue, 21 Nov 2017 04:48:19 GMT

I haven't see any difficult in your expression, What are you getting when you use same as your expression

Or else, Try this way

Avg(Aggr((Sum(Rate*Factor)/Sum(Rate)), AREA, [AREA 2], [PRODUCT CODE]))

Re: Pivot Table (weighted Avg) issue.

settu_periasamy — Tue, 21 Nov 2017 06:30:43 GMT

Hi saurabh,

may be try this..

if(Dimensionality()=3,sum(factor),
Sum(TOTAL<[AREA 2]>Rate*factor)/Sum(TOTAL <[AREA 2]>Rate))