8 Replies Latest reply: Jun 18, 2012 4:06 PM by JOSE MARIA TOS MIÑANA RSS

    Question about average calculation

    Guan Wang



      I have trouble calculating the averages per fiscal year, would anyone help?

      This is my case:

      I have loaded individial product info at the beginning of fiscal years and end of the fiscal years. But I need to get an average of the two date points, in order to calcalate the percentage for each fiscal year. Does anyone knows how to do this?


      Thank you!

        • Re: Question about average calculation
          Johannes Sunden



          Would you be able to share a document with some sample data to give us an understanding of your data model which would make this a lot easier to answer?




            • Re: Question about average calculation
              Guan Wang

              Hi Johannes,


              Thanks for the suggestion.


              Here is my case:

              I have table A, listing all products at the beginning of fiscal years and end of the fiscal years:

              Time                       Product #

              BeginningFY09        1

              BeginningFY09        2

              BeginningFY09        3

              BeginningFY09        4

              BeginningFY09        5

              BeginningFY09        6

              BeginningFY09        7

              EndFY09                1

              EndFY09                2

              EndFY09                3

              EndFY09                4

              EndFY09                5

              EndFY09                6

              EndFY09                7

              EndFY09                8         

              EndFY09                9     

              BeginningFY10       1

              BeginningFY10       2

              BeginningFY10       3

              BeginningFY10       4

              BeginningFY10       5

              BeginningFY10       6

              BeginningFY10       7

              EndFY10                1

              EndFY10                2

              EndFY10                3

              EndFY10                4

              EndFY10                5


              I need to calculate average per each fiscal year, by using (BeginningFY + EndFY)/2, say Count(FY09)=8 and Count(FY10)=6. I don't know how to write a function in order to get an average value for each fiscal year, based on beginning of year and end of year values.


              Then in Table B, I have the bad product info.

              Time                       Bad Product #

              FY09                      1

              FY09                      3

              FY10                      2

              FY10                      6


              I want to create a chart based on table B, and calculate the bad product percentage, by using

              Bad_Product_Percentage(FY09) = Count(Bad Product #s in Table B of FY09)/Count(FY09) = 2/8=25%

              Bad_Product_Percentage(FY10) = Count(Bad Product #s in Table B of FY10)/Count(FY10) = 2/6 = 33%


              This can be done under expression tab of the chart, but I don't know how to get the Count(FY09) or Count(FY10), as listed above,


              Appreciate your help.


              Thank you!