8 Replies Latest reply: Jun 18, 2012 4:06 PM by JOSE MARIA TOS 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

                    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

                            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