2 Replies Latest reply: Mar 21, 2013 8:20 AM by Mikael Gerhardsson RSS

    Average of column within the same quarter

      Hi!

       

      I need to calculate an average value of a column [Cost]. My reports are excelfiles which have the following structure:

       

       

      DateCost
      2012-01-31aaa
      2012-01-31

      bbb

      2012-01-31ccc
      2012-01-31ddd
      2012-01-31eee

       

      Date are always the last date of a month and there are one file for each month, so averaging over the column is not a problem with avg(Cost). I have imported all these files into a table in qlikview.

      My problem is that I need a formula for calculating the average cost within the same quarter (up to that date)...Example:


      Date Cost

      2012-01-31    

      1

      2012-01-31    

      1

      2012-01-31    

      1

      2012-02-28    

      2

      2012-02-28    

      2

      2012-02-28    

      2

      2012-03-31    

      3

      2012-03-31    

      3

      2012-03-31    

      3

      2012-04-30    

      4

      2012-04-30    

      4

      2012-04-30    

      4

       

      The formula should show:

      January: avg(Cost) = 1+1+1/3 = 1

      February: avg(Cost) = 1+1+1+2+2+2 / 6 = 1.5

      March: avg(Cost) = 1+1+1+2+2+2+3+3+3 / 9 = 2

      April: avg(Cost) = 4+4+4/3 = 4

       

      I have tried load all data from above table into a new one and add a new column for which Quarter it is: load .. 'Q'&Ceil(Date/3) & '-' & Year(Date) as QY and then use: (not correct QV syntax here)

       

      avg({<QuarterYear >= {QY}>} Cost)

       

      But this depends on that I select a Quarter... I am kind of stuck, can someone give me some hints on how I should think about this problem?

       

      Best regards

      Mikael

          • Re: Average of column within the same quarter

            Thank you Gysbert! That works.

             

            Unfortunately the requirements was missunderstod and what we need is now is only the average with in the same quarter. Which means if Jan, Feb and March data is available then the formula should calculate the average of these month and show the same value for each month.

             

            Example:

            If all months in a specific quarter data is available:

            formula calculates same result for each month wich is average of cost for these months.

             

            Jan: (sum of cost for every month in this quarter)/count

            Feb: same as Jan

            March:Same as Jan

             

            If only we have the first month in a quarter of data available:

            formula calculates avg of one month.

             

            If only data for two months the formula needs to calculate same result for these two which is the average of them.

             

            Hope you understand what I mean... I am sure your code can be modified in an easy way...

             

            Best regards and Thanks.

            /Mikael