2 Replies Latest reply: Jul 1, 2011 6:40 AM by ben weeks RSS

    Averages ignoring zero values



      I recently posted a question on Averages which fixed my problem at the time. However, what I have found is that it does not work for some of my calculations, as it omits the zero records


      I have a table that looks as follows:


                                                           Week_No   |  1   |  2   |  3  |  4  |  AVG 

      Employee ID  |   Employee Name  | 

      1                         Ben                                        2      2      3     3     2.5

      2                         Alan                                       2      2      0     2     2


      What I am expecting to see, is that Alan's average should be: 6/4 =  1.5, not 6/3 = 2, so that it does not omit the zero values


      The calculation I am using in this case is:

      count({$<WAGE_CODE_NAME>}  ACCOUNT_DATE / count(distinct WEEK_NO)


      Week_No is being used as the dimension with 'Partial Sums' selected to provide the average       


      Many Thanks


        • Averages ignoring zero values
          Erich Shiino

          In your table, the zero value really exist? Or it is created in the table?

          Maybe you could change the expression a little bit, so all the employees have the same number of weeks as reference:

          count({$<WAGE_CODE_NAME>}  ACCOUNT_DATE / count(distinct WEEK_NO)count({$<WAGE_CODE_NAME>}  ACCOUNT_DATE / count(distinct total WEEK_NO)


          Hope this helps,