Weighted Average (pivot table)

[bartvanwetten]

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

[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.

[JonnyPoole · Accepted solution]

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]

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