Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I am using QV11 and having trouble with an expression.
My issue is: I have a table loaded with Dept, subDept, product_code, empid, and emp_name. I need to get a headcount of each employee in the main department. When I create the chart (straight table), I create these dimensions and use count(distinct(empid)), it appears that QV is counting the distinct empid within each subdept and product_code - effecitvely counting duplicate empids. The output has to include the subdept, product_code, and headcount fields.
My output right now is inflated due to employees listed multiple times (they can be in multiple subdepts)…I just need to count the distinct number of empid’s w/in the main department. I need the expression to ignore subdept and product_code. I feel like some type of set analysis applies here, but not sure what to do. Does anyone have any advice/suggestions?
Thank you in advance.
Hi Jason,
can you post some example data for this, say in excel?
Cheers
Andy
I can't find a way to attach a file, so here is a sample table:
Dept ID | Product Code | empid | EMPNAME |
5009005001 | 8000 | 1234567 | xxx, yyyy |
5009005001 | 8000 | 1234568 | xx,yyy |
5009005466 | 8000 | 1234569 | x, y |
4254644333 | 3500 | 2345678 | xxxx, xxxx |
4254924224 | 3500 | 2345678 | xxxx,xxxx |
4254644222 | 3500 | 3456789 | xxx,xxx |
4254644333 | 3650 | 3456789 | xxx,xxx |
In QV, this gives me a headcount of 7. | |||
It's actually 5 employees, by counting distinct empids. |
Hi Jason,
you said "The output has to include the subdept, product_code, and headcount fields"
In this case, the answer cannot be different: if there's more than a combination of empid + Product Code + Dept ID you'll see all of them.
If on that table you only want to see once each empid you must avoid other dimensions.
Cheers
Luca
... and the definition of main department is...?
Try this:
=Count(DISTINCT empid)
This will only count the distinct occurrences of each Employee id.
HTH
Best,
Sunny
What Luca mentioned is correct.
You can do it with one dimension but not multiple dimensions.
Regards
Koushik Vutha
I was thinking set analysis could manipulate the expression independent of the dimensions. At any rate, a big thanks to all who replied. I figured it out. The datasource contained additional departments that was defined before. Their numerical range happened to fall w/in a range of the departments I was testing. So, it's all good now. Thanks again.
PS - if you want to attached something to a post, then click on 'Used advanced Editor' and away you go!
Andy