Skip to main content
Announcements
Qlik Community Office Hours, March 20th. Former Talend Community users, ask your questions live. SIGN UP
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

How to weigh/multiply records on row level and then get correct average?

We have a problem how to weigh on row level.


We want to weigh one field based on another field.
NumberOfPersonsUsed should be weigh on how much planned production time (PlanProdTime in seconds) the row has. The reason is that number of persons could differ between different times and we should show how much persons have been used for each planned hour in average for the order.


We have tried the following:

=(SUM(NumberOfPersonsUsed*PlanProdTime)/(Sum(PlanProdTime)))
and
=(SUM({$<PlanProdTime={">0"}>}NumberOfPersonsUsed*PlanProdTime)/(Sum(PlanProdTime)))

I.e. to multiply each row first and then divide on sum of PlanProdTime.

But they give a value over 1 for the below example, around 1.1. I.e. it is not weigh correctly. It seems to sum all NumberOfPersonsUsed before it multiplies with planProdTime.

Any suggestions how to do this?

Order_Id NumberOfPersonsUsed PlanProdTime Hour Day
1290  1  0        17 1
1290  1  494    17 1
1290  1  1800  17 1
1290  1  3600  18 1
1290  1  0        19 1
1290  1  2700  19 1
1290  1  0        20 1
1290  1  2700  20 1
1290  1  3600  21 1
1290  1  0        22 1
1290  1  900    22 1
1290  1  1800  22 1
1290  1  3600  23 1
1290  1  0         0  2
1290  1  1800   0  2
1290  1  0         1  2


Very greatful for answers!

Best Regards,
Claes

1 Reply
swuehl
MVP
MVP

Just using your two expressions, I do get 1 back.

Is your setting maybe more complex than shown?