6 Replies Latest reply: Jun 5, 2012 4:12 AM by Pierre Philouze RSS

    Cumulative average

      Hello!

       

      I have a straight table wth average of marks by month. Now I want to have the cumulative average of marks of the 6 last months. I can't do this in the loading script because average must be calculate according to the selections.

       

      I tried this but it doesn't work : round(avg({$<[MONTH]={">=$(=above(MONTH,6))<=$(=above(MONTH,0))"}>}[MARK]),0.01)

       

      Regards,

        • Cumulative average
          Karl Pover

          If the months are going to be visible, you can use the rangeavg() function.

           

          rangeavg(above(sum(Mark),0,6))

           

          The above(...,0,6) will return a list of the 6 last values including the value on the current row.

           

          Karl

          • Cumulative average
            Maxim Senin

            Hi,

             

            Don't you have a clue on how to calculate average sum of values (say, number of sold units) between monthes of several years?

             

            For instance, we have the following table:

            Unit     Date     Value

            A     2010-01-01     1045

            A     2010-01-02     23

            A     2010-02-01     523

            B     2010-02-02     54

            A     2011-01-01     32

            A     2011-01-02     2414

            A     2011-02-01     53

            B     2011-02-02     462

             

            I'd like to calculate avg. sum of Value for unit A in the month 01 for all the years, i.e., avg. between 1045+23 and 32+2414, but not avg. between 1045, 23, 32, 2414.

             

            Thanking you in advance.

            • Cumulative average

              Another question : when I got some selections and not values for all months, my average is not good. Example :

               

              period   mark

              201109          7,5

              201110          7,5

              201110          10

              201111          0

              201112          7,5

              201112          7,5

              201112          7,5

              201201          7,5

              201201          2,5

              201204          2,5

               

              For the 201204 period I should have 5 but I got 6 (6 rolling months average). Qlikview take the last 6 months with values and not the 6 real months... I tried to check the 'show all values' box but it still not working :-(