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

    Question about average calculation

    Guan Wang

      Hi,

       

      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

          Hi,

           

          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?

           

          Cheers,

          Johannes

            • 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!