5 Replies Latest reply: Apr 6, 2015 8:59 PM by James Kerrigan RSS

    Weighted Averages in Qlik Sense

    James Kerrigan

      Hey guys,

       

      I'm trying to create a measure which multiples the number of existing titles against a fee amount, and then divides this calculated amount against the number of rows of that particular title. Basically I'm trying to distribute the fee equally to each company holding that particular title, which I think is a weighted average.

       

      TitleCompanyFee
      ACompany A$900
      ACompany B$900
      ACompany C$900
      BCompany A$1000
      BCompany C$1000
      CCompany B$400
      DCompany C$1200
      DCompany D$1200

       

      For example, Company A would result as so:

       

      Count(Title) * Fee

      Count(A) * 900

      1 * 900 = 900

      900 / 3 = 300

       

      Count(Title) * Fee

      Count(B) * 1000

      1 * 1000 = 1000

      1000 / 2 = 500

       

      So Company A's Total would be 800 instead of 1900.

       

      I thought the correct expression would be this:

      (Count(Title) * Fee) / Aggr(Count(Title),Title)

       

      But it doesn't seem to be working for all rows and I'm stuck on what to do.

       

      Thanks in advance to anyone who figures it out!

       

      EDIT:

       

      Hey guys sorry for opening this up again but I'm still having problems.

       

      After trying out the solution I've discovered that it still doesn't work how I like it to, I don't think I did a good job of explaining (didn't include the Title_Type table) so I'll try again.

       

      I have 2 tables:

       

      Title_TypeFee
      AA$900
      BB$1000
      CC$800

       

      TitleTitle_TypeCompany
      AAACompany A
      AAACompany B
      AAACompany C
      BBBCompany A
      BBBCompany C
      CAACompany B
      DBBCompany C
      DBBCompany D

       

      Now for example if Title_Type AA has a fee of $900 and Title A has 3 companies sharing it, Company A should have 1/3 of that fee and have $300.

      And then as Title_Type BB has a fee of $1000 and Title B has 2 companiies sharing it, Company A would have 1/2 of that fee and have $500.

       

      The total then would be $800 for Company A.

       

      The current formula which has been suggested:

       

      Sum(Current_Fee) / Count(distinct Company)

       

      Doesn't seem to be working for this. Instead it's summing up the fees of titles without repeating summing up the fees of recurring title_types.

       

      What I need it to do is divide the fee for the title by the number of company's with that same title, and then add up all calculated fees.

       

      Sorry if I haven't explained this well. Thanks to anyone who can solve this problem for me!

        • Re: Weighted Averages in Qlik Sense
          Henric Cronström

          With the above table as source data, you will have problems. The $900 of Title A will be counted three times since there are three records. And this is not what you want.

           

          If you instead have one table with Title & Fee - one record per title - and one table with Title & Company, it becomes a lot easier:

           

          Then you can use Title as dimension and

               Sum(Fee)/Count(distinct Company)

          as measure.

           

          HIC

            • Re: Weighted Averages in Qlik Sense
              James Kerrigan

              Thanks! It appears to be working at the moment but I haven't put in my set analysis yet. I'll have to check back tomorrow. Thanks again!

              • Re: Weighted Averages in Qlik Sense
                James Kerrigan

                Very sorry Henric, that solution isn't working for me but that's most likely due to how badly I explained my problem.

                 

                I edited my post to hopefully better explain what I'm trying to do. Hope you can help!

                  • Re: Weighted Averages in Qlik Sense
                    Henric Cronström

                    This is a case where I think it is better to do the calculations in the script. Basically, you want to create a table where you calculate the fee for each combination of Title and Company. So...

                     

                    [Price List]:
                    Mapping Load * Inline
                    [Title_Type, Fee
                    AA, 900
                    BB, 1000
                    CC, 800]
                    ;

                    tmpBreakdown:
                    Load * Inline
                    [Title, Title_Type, Company
                    A, AA, Company A
                    A, AA, Company B
                    A, AA, Company C
                    B, BB, Company A
                    B, BB, Company C
                    C, AA, Company B
                    D, BB, Company C
                    D, BB, Company D]
                    ;

                    Left Join (tmpBreakdown)
                    Load
                    Title,
                    Count(Company) as NumberOfCompanies
                    Resident tmpBreakdown
                    Group By Title;

                    Breakdown:
                    Load *,
                    ApplyMap('Price List',Title_Type,0) / NumberOfCompanies as Fee
                    Resident tmpBreakdown;

                    Drop Table tmpBreakdown;

                      • Re: Weighted Averages in Qlik Sense
                        James Kerrigan

                        Hi Henric,

                         

                        I'm very sorry I should have clarified, the data I posted in this thread was just example data to simplify explanation and conceal confidentiality. The tables posted above belong to two excel spreadsheets (the second sheet containing about 1000 rows) which I've loaded into Qlik Sense.

                         

                        Unfortunately the method you've provided does not work for me for this reason, but I am still a very early beginner to all this and am still learning, and every bit of help you give me helps me learn further so I thank you for that!