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!