topic Re: Expression for weighted average? in QlikView

Expression for weighted average?

Thu, 04 Feb 2016 21:47:48 GMT

Hi - I need to calculate the sum of three Departments' downtime based on metrics per Line. I need to calculate it as a weighted average as each Department has a different number of Lines.

Can anyone tell me what is wrong with my syntax?

=Sum({<Department = {('Dept1'/6), ('Dept2'/3), ('Dept3'/9)}>}Percentage))

Thank you!

Re: Expression for weighted average?

oknotsen — Thu, 04 Feb 2016 21:56:28 GMT

You are trying to divide dimension values by 6, 3 and 9, not the sum of the measures related to those values.

Split that thing into 3 (sub-) expressions (one for each dept) and divide the result of each expression to the right weight. Add those together.

Re: Expression for weighted average?

swuehl — Thu, 04 Feb 2016 21:58:50 GMT

That's hard to tell without knowing something about your model, fields, data values.

What are ('Dept1'/6), ('Dept2'/3), ('Dept3'/9)? Field values in Department?

Maybe try something like

=Sum({<Department = {'Dept1','Dept2','Dept3'} >} Downtime)

/ Count({<Department = {'Dept1','Dept2','Dept3'} >} Downtime)

Re: Expression for weighted average?

Thu, 04 Feb 2016 22:13:23 GMT

Could you show me please? I'm not getting the syntax right.

So this is basically what I need:

Sum({<Department = {'Dept1'}>}Percentage) /6
Sum({<Department = {'Dept2'}>}Percentage) /3
Sum({<Department = {'Dept3'}>}Percentage) /9

How do I add them together? Sorry - I'm learning!

Re: Expression for weighted average?

Thu, 04 Feb 2016 22:22:17 GMT

"Department" = 'Dept1', 'Dept2', and 'Dept3'

"Percentage" is the metric I need to sum for all three departments.

Dept1 has 6 Lines, Dept2 has 3 Lines, and Dept3 has 9 Lines. I need to sum "Percentage" as a weighted average, because each department has a different number of Lines.

Re: Expression for weighted average?

oknotsen — Thu, 04 Feb 2016 22:24:35 GMT

Put them all in 1 line and put a + symbol between them.

Just a best guess of course, as I don't know your data model and also am not 100% sure about what you are aiming at.

Re: Expression for weighted average?

Thu, 04 Feb 2016 22:35:18 GMT

Thank you!

Re: Expression for weighted average?

oknotsen — Thu, 04 Feb 2016 22:38:42 GMT

Happy I could help .