1 Reply Latest reply: Sep 12, 2017 10:02 PM by Padmashini Ramaraj RSS

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

    Padmashini Ramaraj

      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

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

          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)