6 Replies Latest reply on Feb 19, 2013 2:29 AM by Jim Wahl

    Weighted calc

    Hema sekar

      Hi,

        Please help me in resolving this weighted calc problem. I have a table which holds records with dimension ATTR1 as A,B,C where either col A or col B will hold value at a time.

      I need to multiply COL A with COL B and divide it by sum of COL B as shown in the below calculation.

      Thus, I would be able to derive a single record as an output of this. can anyone help me in achieving this?

      As

       

      Attr1Col ACol B



      A10




      B20




      C
      30




















      CALCULATION = (col a * col b)/sum(col b)
        • 1. Re: Weighted calc
          Jim Wahl

          Hi Hema,

           

          I'm a little confused by your example. What is being multiplied? 10*20*30 / 30

           

          Jim

          • 2. Re: Weighted calc
            Dana Withers

            Hi Hema,

             

            I'm also a little confused  - perhaps different numbers explain the calculation better.

            Best guess... I think you are looking for something like

             

            total (sum([Col A]) * sum([Col B]) ) /  total( sum([Col B]) )

             

            or

             

            (window_sum(sum([Col A])) * window_sum(sum([Col B])) ) /  ( window_sum(sum([Col B])) )

             

            Hope that helps,

             

            Dana

            • 3. Re: Weighted calc
              Hema sekar

              Thanks Dana and Jim for your reply.

              Let me give more details on the issue I am facing with. I have a table something similar to given below.

              For each and every dept,  I will have one record with Name as NA which would hold value for Dept Wght column. I need to use that column value to multiply with Emp sal column for each and every record with same Dept.

              Calc = sum(Emp Sal * Dept Wght)/ sum(Dept Wght)

               

              Thus if I create a report at Dept level , it should give me for Dept A = 6000/10 = 600

              output:

              DeptWght
              A60
              B60

               

              Table:

              DeptNameEmp SalDept wght
              AHema100
              AJohn200
              AKevin300
              ANA
              10
              BMary10
              BMae20
              BFrank30
              BNA
              20

               

              If I try to sum up dept wght value then multiply with Emp Sal column it does not give me the right figure. Any help appreciated.

               

              Thanks

              Hema

              • 4. Re: Weighted calc
                Jim Wahl

                Hi Hema,

                 

                Thanks for the explanation. I'm still a little confused and I think I'm missing part of the problem. I think Dept A in the output table above should be 600 to match the preceding text?

                 

                But the main confusion is that your formula

                     SUM(Emp Sal * Dept Wght)/SUM(Dept Wght) 

                == SUM(Emp Sal * Dept Wght)/Dept Wght   (only one Dept Wght / dept)

                == SUM(Emp Sal)

                 

                 

                 

                Jim

                • 5. Re: Weighted calc
                  Hema sekar

                  Hi Jim,

                    Yes you are right. It is 600.

                  I apologize for the typo error. To answer on your second question, denominator is the sum(dept wght).

                  If you could help me in achieving numerator that would be great.

                  SUM(Emp Sal * Dept Wght)/Dept Wght   (only one Dept Wght / dept)

                   

                  Thanks

                  Hema

                  • 6. Re: Weighted calc
                    Jim Wahl

                    Given your example, SUM(Emp Sal * Dept Wght)/SUM(Dept Wght) is equivalent to SUM(Emp Sal). This will hold true as long as there is only one Dept Wght (x) value per department --- (100*x + 200*x + 300*x)/x == (100 + 200 + 300)*x / x = (100 + 200 + 300)

                     

                    If you take your example data, open it in Tableau, double-click on Dept, double-click on Emp Sal. You'll get your example output -- sum of Emp Sal by department.

                     

                    So I'm still confused  by what you're trying to calculate.

                     

                    Jim