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: 
bartvanwetten
Creator
Creator

Weighted Average (pivot table)

Hi,

can somebody tell me what expression to use to get the Weighted Average in the total line in attached qlikview document.

The attached excel file shows the calculation and the right Weighte Average (14.78%)

Thanks in advance!

Bart

1 Solution

Accepted Solutions
JonnyPoole
Employee
Employee

i changed your expression to the following and the total is now 14.71.  Your excel says 14.78 , but then i noticed cell D7 show 14.86 in excel and qv shows 14.56 for the same cell. So there may be some other issue.

Your chart is very custom where there is a 'total' line in the data of your 2nd dimension. if there was no 'total' and you used 'partial sums' on the presentation tab and allowed QV to calculate the total, only then would the dimensionality() function work.  (secondarydimensionality() for pivot tables). 

hope it helps

if( avg( {<Periode=, period=, period = {"<=$(vRapportagePeriode)"}, Rekening_Groep = {'Aankoop voer'}>}if( r4_ds <> 0, r4_ds ) ) <> 0,

avg( {<Periode=, period=, period = {"<=$(vRapportagePeriode)"}, Rekening_Groep = {'Aankoop voer'}>}if( r4_ds <> 0, r4_ds ) ) & '%'

)

View solution in original post

3 Replies
Gysbert_Wassenaar

The value in your excel file is 14.78% because the percentages are rounded. If you export your table from qlikview to excel and do the same calculations with the unrounded percentages you will get 14.71%. Just as qlikview does.


talk is cheap, supply exceeds demand
JonnyPoole
Employee
Employee

i changed your expression to the following and the total is now 14.71.  Your excel says 14.78 , but then i noticed cell D7 show 14.86 in excel and qv shows 14.56 for the same cell. So there may be some other issue.

Your chart is very custom where there is a 'total' line in the data of your 2nd dimension. if there was no 'total' and you used 'partial sums' on the presentation tab and allowed QV to calculate the total, only then would the dimensionality() function work.  (secondarydimensionality() for pivot tables). 

hope it helps

if( avg( {<Periode=, period=, period = {"<=$(vRapportagePeriode)"}, Rekening_Groep = {'Aankoop voer'}>}if( r4_ds <> 0, r4_ds ) ) <> 0,

avg( {<Periode=, period=, period = {"<=$(vRapportagePeriode)"}, Rekening_Groep = {'Aankoop voer'}>}if( r4_ds <> 0, r4_ds ) ) & '%'

)

bartvanwetten
Creator
Creator
Author

Dear Jonathan,

Thanks;

typo indeed in my Excel for that column D7.

I saw that qlikview would weight the average automatically instead of averageing the single rows. I simplified the expression now and just not show the grand total for ds%and let qlikview do the weighting for the ds% subtotal.

Thanks again!

Bart