Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Join us to spark ideas for how to put the latest capabilities into action. Register here!
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)