Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

padmashini_rama
New Contributor

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

Hi All,

Please help me on the Average on top of sum function, scenario as follows:

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,

Padma

1 Reply
padmashini_rama
New Contributor

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)