Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
settu_periasamy
Master III
Master III

weighted average formula

Dear All,

Please see the below example. I need the weighted average based on the columns.

ABCDEFG
Sales OrgWGColumn 1Column 2Column 3Weighted Averageexcel formula
Total57,0004042sumproduct(F1:F5,C1:C5)/sum(C1:C5)
110co40,2062528-9.6%=D1 / E1 -1
210ch14,2006062-2.1%=D2 / E2 -1
310se1,36112411210.4%=D3 / E3 -1
410eq1,0661941788.5%=D4 / E4 -1
510in16728923821.2%=D5 / E5 -1

Need the expression for the weighted average only (Total Row - Yellow text) based on the column .

And i have attached the sample.

i tried the dimensionality(). if (dimensionality() = 0,sum(weighted average * column1)/sum(column1), 'ok').

but not working. is there any chance to sum the column values like sum(total column(n)).

Help Needed.

Regards,

Settu

1 Reply
swuehl
MVP
MVP

Try advanced aggregation in your expression for dimensionality() = 0:

=if(dimensionality()=0,

sum(aggr((sum([Column 2]) / sum([Column 3]) -1)*[Column 1], [Sales Org],WG)) / sum([Column 1])

,sum([Column 2]) / sum([Column 3]) -1)