
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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)
