5 Replies Latest reply: May 27, 2012 6:54 PM by Stefan Wühl RSS

    Average over three tables

      Hello to everybody

       

      I tried to calculate weighted average values over three table. Unfortunately I didn't suceed, thus I hope to find help here.

       

      Simplified explanation of the situation. I have a market research database with three tables.

      • Persons: Discribes the sample persons with their attributes
      • Articles: Discribes different newspapers articles
      • Ratings: Is the result table combining Persons and Articles and presenting how persons rated the articles

       

      For our case the following attributes are relevant

      • Persons: PersonId, Gender
      • Articles: ArticleId, LengthOfArticle
      • Ratings: PersonId, ArticleId, Rating

       

      In my diagramm I try to calculate an average Value of the Ratings - weighted by the LengthOfArticle. I tried the following formula

       

      sum(LenghtOfArticle * Rating) / sum(LengthOfArticle).

       

      The calculated "weighted average" is not right - far too high.

       

      Can anybody help me?

       

      Thank you very much

      Angy

        • Re: Average over three tables

          I just attached a demonstration file.

           

          I can see the raw data as well as the weighted average in the diagramm. The weighted average (129.78) is higher than the highest single value. The weighted average should be 43.26.

           

          Any ideas? Thank you very much for your help...

           

          Untitled-3.jpg

           

          Untitled-5.jpg

            • Average over three tables

              Hi again

               

              I found out what the problem is. Sum(Rating*Length) is calculated in the middle-table (Ratings); Sum(Length) is calculated in the side-table "Tabelle1$-1" (Articles), which has less entities.

               

              The right value is calculated if I use the following expression:

               

              sum(LenghtOfArticle * Rating) / sum(LengthOfArticle * Rating / Rating).

               

              Is there a better way to calculate the right weighted avg. value?

               

              Thank you

                • Average over three tables
                  Stefan Wühl

                  Angela86,

                   

                  you could consider just joining the Length field to your Ratings table, this keeping the measures / facts you need for your calculation in one table.

                   

                  If you don't want to do that, you could use advanced aggregation to achieve the correct result, try something like

                   

                  =sum(aggr(sum(Rating*Length),ArticleId,PersonId))/sum(aggr(sum(Length),ArticleId,PersonId))

                   

                  Yor should be you able to replace the numerator with your original version

                   

                  =sum(Rating*Length) / sum(aggr(sum(Length),ArticleId,PersonId))

                   

                  or your version which is even shorter, but I think the syntax above might express more clearly what you are trying to achieve (evaluating the sums in which contexts).

                   

                  Regards,

                  Stefan

                    • Average over three tables

                      Hi Stefan

                       

                      Thank you very much for your answer.

                       

                      I am relatively new to QlikView, thus my question. How do I Join the Lenght attribute to my Ratings table?

                       

                      Greetings

                      Angela

                        • Average over three tables
                          Stefan Wühl

                          You do a join of tables in the load script, so in your example, you could do it like this:

                           

                          Rating:

                          LOAD PersonId,

                               ArticleId,

                               Rating

                          FROM

                          [C:\Dokumente und Einstellungen\Kilian\Desktop\Results.xls]

                          (biff, embedded labels, table is Tabelle1$);

                           

                          LEFT JOIN (Rating) LOAD

                                     ArticleId,

                               Length

                          FROM

                          [C:\Dokumente und Einstellungen\Kilian\Desktop\Articles.xls]

                          (biff, embedded labels, table is Tabelle1$);

                           

                          Persons:

                          LOAD PersonId,

                               Gender

                          FROM

                          [C:\Dokumente und Einstellungen\Kilian\Desktop\Persons.xls]

                          (biff, embedded labels, table is Persons$);

                           

                          I also labelled the tables (this is always a good idea, some script statements needs a table name, and it's easier to set one yourself.

                           

                          If your article table has a lot more information about the specific articles than Length, you could add another load of the articles table, but then without the Length field:

                           

                          Articles:

                          LOAD

                            ArticleId,

                            ArticleName,

                             Manufacturer

                          FROM

                          [C:\Dokumente und Einstellungen\Kilian\Desktop\Articles.xls]

                          (biff, embedded labels, table is Tabelle1$);