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!

      • Re: Question about average calculation
        Guan Wang

        Please help

      • Re: Question about average calculation
        JOSE MARIA TOS MIÑANA

        There are a few ways todo that better but in a simple case this should works

         

        Avg would be:

         

        (count({< Time = {'BegginningFY09'}>}distinct Product)+

        count({< Time = {'EndFY09'}>}distinct Product))/2

         

        Percentage:

         

        count({< Time = {'BegginningFY09'}>}distinct [Bad Product]) / count(total [Bad Product])

         

         

        Hope this helps

        • Re: Question about average calculation
          Guan Wang

            I have another reference table in another tab:

          such as

           

          FinancialYearRange:

          LOAD * INLINE [

          BegginningFY, EndFY, FinancialYearRange

          01/01/2009, 12/31/2009, FY-09

          01/01/2010, 12/31/2010, FY-10

          ];

           

           

          When I wrote the following expression in QlikView,

           

          =(Count(If(<Date

          (Time,'MM/DD/YYYY') = {'BegginningFY'}>) DISTINCT Product) + Count(If(<Date(Time,'MM/DD/YYYY') = {'EndFY'}>) DISTINCT Product))/2

           

          It throwed an error. I am not sure where the problem is. QlikView software is not easy to debug.

          • Re: Question about average calculation
            JOSE MARIA TOS MIÑANA

            Time must be a date type field, I´m seeing that the values of Time are words in table A and table B, so what fields do you want to compare??

             

            You must be have a field Time with Dates with the same format that BegginningFY and EndFY.

             

            So, you must have something like this:

             

            Time

            18/06/2012

             

            and then, in this expression you have the comparison between 18/06/2012 and your dates on BegginningFY and EndFY fields.

             

            (count({< Time = {"=BegginningFY"}>}distinct Product)+

            count({< Time = {"=EndFY"}>}distinct Product))/2

             

            Regards