
1. Re: Weighted calc
Jim Wahl Feb 18, 2013 7:07 AM (in response to Hema sekar)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 Feb 18, 2013 7:48 AM (in response to Hema sekar)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 Feb 18, 2013 11:41 PM (in response to Dana Withers)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:
Dept Wght A 60 B 60 Table:
Dept Name Emp Sal Dept wght A Hema 100 A John 200 A Kevin 300 A NA 10 B Mary 10 B Mae 20 B Frank 30 B NA 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 Feb 19, 2013 12:08 AM (in response to Hema sekar)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 Feb 19, 2013 1:21 AM (in response to Jim Wahl)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 Feb 19, 2013 2:29 AM (in response to Hema sekar)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, doubleclick on Dept, doubleclick 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