What’s a Weighted Average and how do you calculate it in QlikView?

    Citing Investopedia, the definition of a ‘Weighted Average’ is “An average in which each quantity to be averaged is assigned a weight. These weightings determine the relative importance of each quantity on the average. Weightings are the equivalent of having that many like items with the same value involved in the average.

     

    Weighted Average.  In Formula, Financial Theory.  Retrieved February 12th, 2014, from http://www.investopedia.com/terms/w/weightedaverage.asp.

     

    I created the below example in Excel to describe the concept:

    Assume you have the following data where you have 5 people, 3 of them are 25 years old and 2 are 28 years old.  You then have weightings assigned to the various values:

     

    step0.png

     

    Step 1 - You would the multiple each value by its weight.  To do this in the example, first multiply each value (Age) by it’s weight (Age * Weight):

     

    step1.png

     

    Step 2 – Get the total value of all the numbers calculated in Step1.

     

    step2.png

     

    Step 3 – Add up the total weighting values

     

    step3.png

     

    Step 4 – Divide the total value of Step 2 by the total weight of Step 3:

     

    step4.png

     

    This number now represents the weighted average.

     

    To perform this same operation in QlikView, you would create a Chart – a Straight Table.

     

    The Dimension would be on the Age

     

    You would then create 2 Expressions – 1 Expression to calculate the Weighted Total over Age and a final Expression that would calculate the Weighted Average.  You would use the AGGR() function to perform this task – this function will return a valule of an expression calculated over a dimension (ie. Get me the total value of Weight calculated over the Dimension Age)

     

    To create the total for the Weight, you would use the following syntax:


    Weighted Total

    Sum(Aggr(Sum(Total Weight), Age))

     

    To create the Weighted Average, you would use the following calculation:

    Sum(Total Age * Aggr(Sum(Weight), Age)) / Sum(Total Aggr(Sum(Weight), Age))