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

# Weighted calc

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

 Attr1 Col A Col B A 10 B 20 C 30 CALCULATION = (col a * col b)/sum(col b)
• ###### 1. Re: Weighted calc

Hi Hema,

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

Jim

• ###### 2. Re: Weighted calc

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

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

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

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

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