# How to use the Average function on top of sum in Qlik straight table?

Hi All,

For finding the Average Annual Premium growth for 10 years, below is the formula used

((sum({<year={2008}>}estimated_amt)/(sum({<year={2007}>}estimated_amt)-1))

+

(sum({<year={2009}>}estimated_amt)/(sum({<year={2008}>}estimated_amt)-1))

+

(sum({<year={2010}>}estimated_amt)/(sum({<year={2009}>}estimated_amt)-1))

+

(sum({<year={2011}>}estimated_amt)/(sum({<year={2010}>}estimated_amt)-1))

+

(sum({<year={2012}>}estimated_amt)/(sum({<year={2011}>}estimated_amt)-1))

+

(sum({<year={2013}>}estimated_amt)/(sum({<year={2012}>}estimated_amt)-1))

+

(sum({<year={2014}>}estimated_amt)/(sum({<year={2013}>}estimated_amt)-1))

+

(sum({<year={2015}>}estimated_amt)/(sum({<year={2014}>}estimated_amt)-1))

+

(sum({<year={2016}>}estimated_amt)/(sum({<year={2015}>}estimated_amt)-1)))

/(count distinct year)

Summing up the (current year amt/(prev year amt -1))+........ In-order to do find average, dividing the summed up values by count of distinct year i.e 10.

Please correct me, if I am using the right formula for finding the average on top of sum function.

Regards,

• ###### Re: How to use the Average function on top of sum in Qlik straight table?

Sorry for the inconvenience!

Below is the corrected formula used:

((1-(sum({<year={2008}>}estimated_amt)/sum({<year={2007}>}estimated_amt)))

+

(1-(sum({<year={2009}>}estimated_amt)/sum({<year={2008}>}estimated_amt)))

+

(1-(sum({<year={2010}>}estimated_amt)/sum({<year={2009}>}estimated_amt)))

+

(1-(sum({<year={2011}>}estimated_amt)/sum({<year={2010}>}estimated_amt)))

+

(1-(sum({<year={2012}>}estimated_amt)/sum({<year={2011}>}estimated_amt)))

+

(1-(sum({<year={2013}>}estimated_amt)/sum({<year={2012}>}estimated_amt)))

+

(1-(sum({<year={2014}>}estimated_amt)/sum({<year={2013}>}estimated_amt)))

+

(1-(sum({<year={2015}>}estimated_amt)/sum({<year={2014}>}estimated_amt)))

+

(1-(sum({<year={2016}>}estimated_amt)/sum({<year={2015}>}estimated_amt))))

/(count distinct year)

Please correct me, if I am using the right formula for finding the average on top of sum function by using count (distinct year)